Skip to main content
cancel
Showing results for 
Search instead 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

Reply

Rolling 12 month calculation - Not all 12 months showing in table

Afternoon all

 

Can someone please help with the following calculations. I'm trying to work out how to do a rolling 12 month calculation

 

KarenFingerhut_1-1662473998699.png


My starting point will always need to go back 12 month even though the previous 12 months arent showing in the table (if that makes sense), So my starting point above is Nov and for that calculation I would need to sum the previous 12 months figures and then so on for each month showing in the table, and then deivide each rolling period by 12 to get the average rolling figure for that month. So

for Nov 20 - Sum the figures for Dec 19 - Nov 20 and divide that by 12

Dec 20 - Sum the figures for Jan 20 - Dec 20 and divide that by 12

Jan 21 - Sum the figures for Feb 20 - Jan 21 and divide that by 12

And so on

 

I then only want to see the previous 12 months in the table 

 

I then need to do the same measure but with a different field, but once I have the measure for the abbove I can easily do this. Then finally I would didide one measure by the other to get the below final table %'s 


Essentially my final table will look like the below

KarenFingerhut_0-1662473894318.png


Is there anyone that can help with the rolling calculation please. I really hope the above makes some sense?

 

Thanks in advance

 

Karen

1 ACCEPTED SOLUTION
davehus
Memorable Member
Memorable Member

Hi @KarenFingerhut ,

 

Dates In Period with help you with this. 

 CALCULATE(Measure),
    DATESINPERIOD('Sales Table'[Docket Date], LASTDATE('Sales Table'[Docket Date]), -365, DAY))
 
Hope this helps.
 
Did I help you today? Please accept my solution and hit the Kudos button.
 
Did I help you today? Please accept my solution and hit the Kudos button.

View solution in original post

3 REPLIES 3
davehus
Memorable Member
Memorable Member

Hi @KarenFingerhut ,

 

Dates In Period with help you with this. 

 CALCULATE(Measure),
    DATESINPERIOD('Sales Table'[Docket Date], LASTDATE('Sales Table'[Docket Date]), -365, DAY))
 
Hope this helps.
 
Did I help you today? Please accept my solution and hit the Kudos button.
 
Did I help you today? Please accept my solution and hit the Kudos button.

Hi @davehus 

 

Thanks so much for getting back to me. This is great, think it's done the trick and I can use for my other measure too, which is ace 😊  . Will do a bit more testing and then come back and sign off as solution 

 

Kind regards

Karen

Thanks @KarenFingerhut , Glad I could be of help. 🙂 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.