cancel
Showing results for
Did you mean:
Highlighted
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

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)

3 REPLIES 3
Highlighted
Super User IV

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://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...

Proud to be a Super User!

Highlighted
Helper I

@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

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)

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### 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