cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

1 ACCEPTED SOLUTION
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

2 REPLIES 2
Helper II

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors