Running total is the same as daily total?

[MelH]

Not sure what I am doing wrong. My running variance is the same as the daily variance. Can someone help? Thanks

Hours 2 = max(Test[Hours 1])
Variance = DIVIDE(sum(Test[Amt]),[Hours 2],0)
VarianceRT = CALCULATE([Variance], FILTER(ALL(Test[Date]),Test[Date]<=MAX(Test[Date])))

 

[GraH]

For a running total your filter should return all dates smaller then or equal to current date. The way you have defined it, it only returns the same data. Hence the same result as for variance.
Define Max(Test[Date]) as a variable first, then return the result via your formula using the variable.
Notice, for time intelligence (YTD, MTD) it is a best practice to use a calendar table.
Maybe this tutorial sheds some light Cumulative total – DAX Patterns

 

[MelH]

For a running total your filter should return all dates smaller then or equal to current date. The way you have defined it, it only returns the same data. Hence the same result as for variance.
Define Max(Test[Date]) as a variable first, then return the result via your formula using the variable.
Notice, for time intelligence (YTD, MTD) it is a best practice to use a calendar table.
Maybe this tutorial sheds some light Cumulative total – DAX Patterns

Thanks, I made some changes and it worked.

 

[smozgur]

Glad to hear you got the solution.

Do you mind posting about what changes you applied to get it to work? Then it is perfectly fine to mark your post as the solution to help future readers.

 

[MelH]

This is what worked.

I created a calendar table (linked it the the Test table) and used the date from that.

I still don't totally understand how it works but will keep having a play with it.

VarianceRT =
VAR DateMax = MAX('Date'[Date])
Return
CALCULATE([Variance], FILTER(ALL(Test[Date]),Test[Date]<=DateMax))

 

[MelH]

How would i change this formula to have a daily running total but reset each month? Or should I start a new post. Thanks

 

[GraH]

MTD should do that.

 

[MelH]

I worked it out. The real data I am working with using a custom calendar.

VarianceRTMth =
VAR DateMax = MAX('Date'[Date])
VAR DateMaxMth = MAX('Date'[Month])
Return
CALCULATE([Variance], FILTER(ALL(Test[Date]),Test[MonthMeasure]=DateMaxMth&&Test[Date]<=DateMax))