Fiscal Month Sums for Rolling 12M

[sjrrkb123]

Suppose I have a field called FiscalMonthYearKey which looks like the following:
201901
201902
201903
201904
201905
201906
201907
201908
201909
201910
201911
201912
202001
202002...up to 202012
is there a way in dax to sum up revenue for the current fiscal month and the preceding 11 fiscal months? So 202011 would go back to 201912, 202010 would go back to 201911 and so on. I know this is easy with calendar dates but my organization really wants to look at fiscal periods. 

[harshnathani · Accepted solution]

Hi @sjrrkb123 ,

You can try this.

Create a Calculated Column which ranks your fiscal period.

Ranking FiscalPeriod = RANKX('Table','Table'[Fiscal Period],,ASC)

Then create a measure

Measure 3 =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Ranking FiscalPeriod]
            <= MAX ( 'Table'[Ranking FiscalPeriod] )
            && 'Table'[Ranking FiscalPeriod]
                > MAX ( 'Table'[Ranking FiscalPeriod] ) - 12
    ),
    'Table'[Value]
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@sjrrkb123 ,

example rolling 12 with date calendar

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[sjrrkb123]

@amitchandak to my knowledge, time intelligence works for calendar dates, not fiscal dates. Our fiscal months do not align with calendar months. For instance, february goes from Feb1 - Mar3. Thus I want to use the FiscalYearMonth keys to do so. 

I have rolling 12m for calendar dates already done and that was easy.