"Rolling 12" measure fails on last value only

[dedelman_clng]

Rolling12UpTime = CALCULATE(
	sum('Date'[UptimeMin]),
	(DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -1, YEAR))
)

Date[UptimeMin] is a simple calculated column:

UptimeMin = calculate(24*60)

The Date table only has dates from 2015 through the current date, as I am using this to show YTD and rolling 12 up through "today".  All of the YTD measures work correctly (using TOTALYTD), but the measure Rolling12UpTime above (using the DAX pattern for "moving annual totals") seems to be doing some subtracting on the last date:

The amount that is missing is curiously exactly 18*1440, which is exactly the number of minutes in remainder of the month after today (1/13/17).

What could be happening here? I just wrote the measure today in the January 2017 update of PowerBI Desktop, in case anyone can produce the correct calculation on an earlier version of desktop.  The Date table could consist of just Date and UptimeMin.

Thanks,

David Edelman

EDITED TO ADD: I bumped up the Date table to have all values through tomorrow (1/14/17).  This fixed the value for 1/13 but the value for 1/14 is now incorrect by 17*1440.

[Sean · Accepted solution]

Try switching to daily (although even this would not always work)

Rolling12UpTime =
CALCULATE (
    SUM ( 'Date'[UptimeMin] ),
    ( DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -365, DAY ) )
)

Good Luck!

[v-shex-msft · Accepted solution]

Hi @dedelman_clng,

Sean's formula seems well, you can also directly use filter to choose the calculated date range

Rolling = CALCULATE(SUM(Test[Amount]),FILTER(ALL(Test),Test[Date]>=DATE(YEAR(MAX(Test[Date])),MONTH(MAX(Test[Date]))-12,DAY(MAX(Test[Date])))&&Test[Date]<=MAX(Test[Date])))

Regards,

Xiaoxin Sheng

[dedelman_clng · Accepted solution]

I ended up using DatesInPeriod and LastDate:

Rolling12UpTime = CALCULATE(
	sum('Rolling Date'[UptimeMin]),
	(DATESINPERIOD('Rolling Date'[Date], LASTDATE('Rolling Date'[Date]), -1, YEAR))
)

[v-shex-msft · Accepted solution]

Hi @dedelman_clng,

Sean's formula seems well, you can also directly use filter to choose the calculated date range

Rolling = CALCULATE(SUM(Test[Amount]),FILTER(ALL(Test),Test[Date]>=DATE(YEAR(MAX(Test[Date])),MONTH(MAX(Test[Date]))-12,DAY(MAX(Test[Date])))&&Test[Date]<=MAX(Test[Date])))

Regards,

Xiaoxin Sheng

[Sean · Accepted solution]

Try switching to daily (although even this would not always work)

Rolling12UpTime =
CALCULATE (
    SUM ( 'Date'[UptimeMin] ),
    ( DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -365, DAY ) )
)

Good Luck!