Rolling 3 months

[olgad]

Hi guys, got stuck, going circles.

this is my data
Year Month Amount MonthNr Monthyear

2022 Oct 5 10 202210 

2022 Oct 5 10 202210

2022 June 4 6 202206

2022 June 10 6 202206

2022 Apr 1 4 202204

2022 March 2 3 202203

2022 Feb 4 2 202202

2022 Jan 5 1 202201

i want the rolling total to be like: April=Jan plus Feb plus March   if there is no data for any of the months, then just take the last three available. Like for Example october here, for that it shall be June plus April plus March

Monthyear Amount Rolling Total

202201 5 0

202202 4 5

202203 2 9

202204 1 5

202206 10 7

202210 5 17

Would appreciate the exact solution because mines are just dont seem to work

RollingTotal =

VAR CurrentMonth = MAX('YourTable'[Monthyear])

var Last3Months=

    ADDCOLUMNS(

        FILTER(

           'YourTable',

            'YourTable'[Monthyear] <= CurrentMonth

        ),

        "MonthTotal",

        CALCULATE(

            SUM('YourTable'[Amount]),

            TOPN(

        3,

            SUMMARIZE(

                Filter('YourTable', YourTable[Monthyear]<CurrentMonth),

                'YourTable'[Monthyear]

            ),

        YourTable[Monthyear],

        DESC

    )

        )

    )

RETURN

SUMX(Last3Months, [MonthTotal])

[nirali_arora]

Try like

Last 3 Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Year Rank]>=max('Date'[Month Year Rank])-3 && 'Date'[Week Rank]<=max('Date'[Month Year Rank]) -1))

[nirali_arora]

Create a separate table with monthyear(yyyymm), month no, year and join it with your table using month year. Assume the separate table is date, create a month year rank in the new table - 

Month Year Rank = RANKX('Date','Date'[Month Year],,ASC,Dense)  //YYYYWW format 

Then create a measure like this 

Last 3 Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Year Rank]>=max('Date'[Month Year Rank])-2 && 'Date'[Week Rank]<=max('Date'[Month Year Rank])))

[olgad]

Hi, thanks, but that gives me a standard rolling total for the 3 months

my requirement first of all that 3 months exclude the current month, meaning june shall be calculated may april march, but if there is no data availble in the preceding 3 months, then the general rule shall be 3 last months with available data, which in this case will make june be equal 7 and october 17