DAX measure running total by month

[dicken]

Hello all,

I know there's a lot of stuff out there about CALCULATE, but I wonder if someone can point me in the right direction,
I've created a running total by each month using DAX as a calculated column. I've also got the same as a measure , the difference is the definition of the variable , in
the measure it seems to need to be max(date ) ;

< Newm2:=VAR Sdate = Table1[Date]

RETURN CALCULATE(SUM([Unit]),Table1[Date]<= Sdate && Table1[Date]> EOMONTH(Sdate,-1)) >

But for the measure I use

Newm2:=VAR maxdate = MAX(Table1[Date])

RETURN CALCULATE(SUM([Unit]),Table1[Date]<= maxdate && Table1[Date]> EOMONTH(maxdate,-1))

I've been trying to understand / visualize (as it is dropped into a pivot table ) how the Calculate affects the variable or if it does at all ? #

Richard

 

[RoryA]

In a calculated column you have row context, since each row has a date in it, so you just want to sum values less than or equal to the date in that row.

In a measure there is no row context, since you are aggregating multiple rows. That means you need to calculate the latest date that matches your current filter context (i.e. the maximum date of all the rows being aggregated for the specific cell the measure is in), then calculate the sum of all the Units up to that date.

Does that make sense?

 

[dicken]

In a calculated column you have row context, since each row has a date in it, so you just want to sum values less than or equal to the date in that row.

In a measure there is no row context, since you are aggregating multiple rows. That means you need to calculate the latest date that matches your current filter context (i.e. the maximum date of all the rows being aggregated for the specific cell the measure is in), then calculate the sum of all the Units up to that date.

Does that make sense?

Hi Rory,
thanks, yes it does make sense, my own thinking was along the right lines, but you've put it very clearly.

RD