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
SriGaG1
Helper II
Helper II

Last 12 Month rolling figures for each month

Dear Experts,

 

I am trying one of a customer request. He need to calculate current month total sales against the last 12 month rolling figures. In a percentage.

The formula to be used is = (CM total sales/ last12 month rolling sales) *100

 

As an example, There is a data set month on month. This should be a line chart. In particular year, If we take a 2022 mar month data point, the value sould be derived as,  (CM 2022 mar value/ 2021 april to 2022 mar value)*100. when it moves to Point april the value for april 2022 should be (CM 2022 april value/ 2021 May to 2022 April value)*100

SriGaG1_0-1666299590073.png

can anyone help me to take out the totl valuea gainst total expenses montly?  A workbook is attached herewith

 

https://drive.google.com/file/d/1vrpzZxJezrJVw0XnwS48CnGqiM4FeT0l/view?usp=sharing

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

Hi @SriGaG1 ,

Did the answers above help you? If so, please consider marking the most helpful answer as a solution, as this will help people searching the forum for similar questions in the future to find answers more quickly. Thanks in advance!

If the problem is still unresolved, you can continue to add details of the problem below and feel free to contact us.

 

Best Regards,
Gao
Community Support Team

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mangaus1111
Solution Sage
Solution Sage

Hi @SriGaG1 ,

 

try this:

 

VAR _MaxDate = MAX('Date'[Date])

VAR _CM total sale = SUM('Table'[SalesAmount])

VAR _last12 month rolling sales = CALCULATE( [_CM total sale],

                                                                          'Date'[Date] <= MaxDate && 'Date'[Date] > MaxDate -365

                                                                        )

RETURN

(_CM total sale/ _last12 month rolling sales)*100

 

Your drive link does not work. If you share some data table, I can do something better for you.

 

Did I answer your question? Mark this post as a solution if I did!                   

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.