Show prior 3 Months Cumulative Total month over month

Power Query
1

I need to show the prior 3 months sales in a month over month fashion and i am not sure if a custom column in the query editor is best or if i should do it in a DAX calculation. an example of what i am trying to accomplish is below.

Cumulative 3 Months

Thank You In Advance

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
3

Try this:

Running Total =
VAR MaxDateInFilterContext =
    MAX ( Dates[Date] )
VAR MonthJump = 3
VAR MaxYear =
    YEAR ( MaxDateInFilterContext )
VAR MaxMonthNum =
    MAX ( Dates[Month Number] )
VAR DatesLessThanMaxDate =
    FILTER (
        ALL ( Dates[Date], Dates[Calendar Year Number], Dates[Month Number] ),
        Dates[Date] <= MaxDateInFilterContext
            && Dates[Month Number] <= MaxMonthNum
            && Dates[Month Number] > MaxMonthNum - MonthJump
            && Dates[Calendar Year Number] = MaxYear
    )
VAR Result =
    CALCULATE ( [Total Sales], DatesLessThanMaxDate )
RETURN
    Result

image
image864Ă—812 42.6 KB

2 Likes
4

@AntrikshSharma, @Melissa,

Really well done! Fascinating to see the very different approaches you each took to the problem.

  • Brian
5

Thank you both for the quick response!
I went with @AntrikshSharma because I have a measure that is filtered to select the specific sales data.

Thank You

6

@AntrikshSharma How can I get this to roll into prior years? I need the data to roll back 3 months no matter what year the month is in.

Thank You,

7

As an alternative, the MonthOffset version doesn’t need a provision for that and will work regardless…

3 Month Cum Sales = 
VAR myMonth = CALCULATE( MAX( Dates[MonthOffset] ), FILTER( ALL( Dates ), Dates[Date] = MAX( Dates[Date] )))
RETURN

    CALCULATE( SUM( Sales[Sales] ),
        FILTER( ALL( Dates ),
           Dates[MonthOffset] <= myMonth &&
            Dates[MonthOffset] >= myMonth -2
        )
    )
8

Use this:

Running Total = 
VAR MaxDateInFilterContext =
    MAX ( Dates[Date] )
VAR Result =
    CALCULATE ( [Total Sales], DATESINPERIOD(Dates[Date], MaxDateInFilterContext, -3, MONTH ) )
RETURN
    Result
1 Like