Show prior 3 Months Cumulative Total month over month

Power Query
2

Hi @jamesg,

Welcome to the Forum!

This is something that DAX is best suited for :wink: although you can also achieve the same in PQ.
Here’s the measure.

3 Month Cum Sales = 
VAR myMonth = SELECTEDVALUE( Dates[MonthOffset] )
VAR FirstMonth = myMonth -2
VAR ListMonths = 
    CALCULATETABLE( VALUES( Dates[MonthOffset] ),  
        FILTER( ALL( Dates ), 
            Dates[MonthOffset] <= myMonth &&
            Dates[MonthOffset] >= FirstMonth
        )
    )
RETURN

COALESCE(
    CALCULATE( SUM( Sales[Sales] ),
        FILTER( ALL( Dates ),
           Dates[MonthOffset] <= myMonth &&
            Dates[MonthOffset] >= FirstMonth
        )
    ),
    CALCULATE( SUM( Sales[Sales] ),
        FILTER( ALL( Dates ),
            Dates[MonthOffset] IN ListMonths )
    ))

The logic revolves around the Extended Date table MonthOffset value. The VAR ListMonths and second Calculate inside Coalesce is to populate the Total row with the last value.

You can find the Extended Date table M code here.
And a collaboration between @BrianJ and me on Time Intelligence here.

Here’s my sample file: eDNA - Prior 3 Months Cumulative Total.pbix (65.1 KB)

I hope this is helpful.

2 Likes