Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
devika
Helper II
Helper II

Rolling 12 months

Hi all,

 

I'm trying to calculate rolling 12 months,

 

Rolling 12months =
CALCULATE(
   [TotalCases],
    DATESINPERIOD('Merge'[CASE_DATE],MAX(Merge_Annual_Mx_Vss[CASE_DATE]),-12,MONTH))
 

What I'm sorting is for Rolling 12 months, 2022Q3 = 2186

                                                           2022Q4 = 2186+1914=4100

                                                          2023 Q1=2186+1914+886=4986

 

 

I'm a bit stumped that this is not working for me, and I am not sure what I'm missing. 

 

devika_1-1677034371939.png

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@devika , Always use the date table joined with date of your table. If possible use the Window function

 

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

Rolling Months Formula: https://youtu.be/GS5O4G81fww

 

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

View solution in original post

2 REPLIES 2
devika
Helper II
Helper II

Thanks, @amitchandak, for your reply and for sharing your knowledge; it worked when I joined the data table.

amitchandak
Super User
Super User

@devika , Always use the date table joined with date of your table. If possible use the Window function

 

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

Rolling Months Formula: https://youtu.be/GS5O4G81fww

 

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.