Last 12 Month rolling figures for each month

[SriGaG1]

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

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

[v-cgao-msft]

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]

Hi,

You may download my PBI file from here.

Hope this helps.

[mangaus1111]

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!