cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Fiscal Month Sums for Rolling 12M

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

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]
)

 

 

1.jpg

 

 

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

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@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. 

Highlighted
Super User III
Super User III

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]
)

 

 

1.jpg

 

 

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors