Trying to SUM a rolling month measure

[JimJim]

Hi Guys,

I have attached a test report with details of my problem. Please note that the problem on my test report is nonsense but it is a very simplified version of my actual problem. Please only be concerned with the rolling months page on the report.

I have 2 measures:

Invoice Amount USD R6M: Rolling 6 months of invoice amount.

Invoice Amount Average: Takes a SUM of the previous 3 months of the R6M measure and divides it by 3 (to smooth out any irregularities with the data)

Problem: When I try to SUM the previous 3 months of the R6M measure it does not return the desired results, in fact, it returns the same value as R6M.

Link to pbix: https://1drv.ms/u/s!ArazqhFmvkSJlmVxJRRpSR1dAG4m?e=m0jo3l

[johnt75 · Accepted solution]

You can use

Invoice Amount Average = 
VAR __lastVisibleDate =
    LASTDATE ( 'Time'[Date] )
VAR __financialPeriod =
    MIN ( 'Time'[YearMonth] ) - 2
VAR __fromDate =
    CALCULATE (
        FIRSTDATE ( 'Time'[Date] ),
        FILTER ( ALL ( 'Time' ), 'Time'[YearMonth] = __financialPeriod )
    )
VAR summaryTable = CALCULATETABLE(
    ADDCOLUMNS( SUMMARIZE( 'time', 'time'[financial_year], 'time'[financial_period] ),
        "@total", [Invoice Amount USD R6M] ), 
    DATESBETWEEN('Time'[Date], __fromDate, __lastVisibleDate)
)
VAR __result = AVERAGEX( summaryTable, [@total] )
RETURN
     __result

[JimJim]

Amazing. Thank you 🙂