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
manishjoshi
Frequent Visitor

Last 12 months over months

Hi Experts,

 

I am looking for a solution where I select any month from the date slicer and I get previous 12 months sales by month and also want percentage variance between two moths.

eg

Selected month - 1/8/21 to 31/8/21
prevous 12 months by months

month      sales     diff          %

sep 20 20    
oct 20 25525.00%
nov 20 31624.00%
dec 20 35412.90%
jan 21 42720.00%
feb 21 4537.14%
mar 21 53817.78%
apr 21 5523.77%
may 21 5947.27%
jun 21 65610.17%
jul 21 7057.69%

 

Thanks

Manish

12 REPLIES 12
Anonymous
Not applicable

Here's what you have to do to achieve the setup you want:

https://youtu.be/d8Rm7dwM6gc

Watch it carefully till the very end.

 

Hi Daxer,

 

Thans for your help. I have watched the vidoe. I am able to achieve last 12 months salses and previous one month's sales but not all last 12 months. I want to display % change from previous month to current month.

 

 I have selected month Jan 2014 in month slicer and measure _TotalSales gives me last 12 months

_TotalSales  = 

CALCULATE( SUM( Sales[TotalSales] ), DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -12 , MONTH ))
 
But _TotalSalesLM gives me only one previous month. I want all previous 12 monts so that I can calculate % change from previous month to current month.
_TotalSalesLM = 
CALCULATE( [_TotalSales], DATEADD( Sales[MonthEndDate], -1, MONTH ) )
 
manishjoshi_0-1632123449918.png

Regards,

 

Manish

Anonymous
Not applicable

Time-intel functions do work only on a well-structured Date dimension, not on any date column, so this is wrong:

CALCULATE( [_TotalSales], DATEADD( Sales[MonthEndDate], -1, MONTH ) )

This is turn is correct because it DOES use a correct Date table:

CALCULATE( SUM( Sales[TotalSales] ), DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -12 , MONTH ))

If you want to know what a well-designed Date table is, you can for instance read this documentation: https://dax.guide/datesinperiod

 

manishjoshi
Frequent Visitor

Hi Sanket,

 

Thank you for your help.

I have no problem gettting lat 12 months sales. I have measure that gives me sales for last 12 or any number I select. My requirement is once I get those last 12 months sales I need percentage of month over month.

 

Regards,

Manish

I can help you with getting percentage of month over month sales.

Use the below steps;

1)SumSales = SUM('Table'[ Sales]).

2)LastMonth = CALCULATE([SumSales],DATEADD('Table'[Date].[Date],-1,MONTH))
3)MoM% = ([SumSales]/[LastMonth]-1)/100.
 
 
Please let me know if that helps.
If it answers your question, then please mark it as 'Accept as Solution'.
 
Regards,
Sanket Bhagwat

Hi Sanket,

 

Thank for your help.

I will change and let you know.

 

Regards,

 

Manish

Hi Sanket,

 

I changed the measure as per your reply but I got only one previous month's sales. I want to attach test file. I am not able to find where to look for.

 

Regards,

 

Manish

You will need to modify the measure which I sent.

Instead of Last Month Sales, add;
Last12months=CALCULATE(SUM('Table'[Sales]),

DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-12,MONTH)

 

Regards,

Sanket Bhagwat

 

Hi Sanket, 

How can I attach my file ?

Please see below .  I have selected month Jan 2014

 

_TotalSales  = 

CALCULATE( SUM( Sales[TotalSales] ), DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -12 , MONTH ))
 
_TotalSalesLM = 
CALCULATE( [_TotalSales], DATEADD( Sales[MonthEndDate], -1, MONTH ) )
 
I am getting only one month but I want all previous montsh

manishjoshi_0-1631779963252.png

 

Regards,

 

Manish

 

It is because you have mentioned in your measure as sales for last month.

_TotalSalesLM = 

CALCULATE( [_TotalSales], DATEADD( Sales[MonthEndDate], -1, MONTH ) ) it will only return last month sales.

_TotalSales  = 

CALCULATE( SUM( Sales[TotalSales] ), DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -12 , MONTH )) this will correctly return last 12 month sales.
 
Regards,
Sanket Bhagwat
 

Hi Sanket,

 

Let me please explain.

 I am getting last 12 months sales based on my month selection in slicer.  what I want to do is - please see image

MonthEndDate  _TotalSales  _TotalSalesLM

31/01/2014   4,798,027.87       4,560,577.10

31/12/2013   4,560,577.10       3,694,668.00
30/11/2013   3,694,668.00       5,374,375.94
and so on

what should be my _TotalSalesLM  measure.

 

Regards,

 

Manish

 

SanketBhagwat
Solution Sage
Solution Sage

Hi Manish.

For getting last 12 months data, you can use DATESINPERIOD function.

 

Create a measure as;
Last 12 months=CALCULATE(SUM('Table'[Sales]),DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-12,MONTH)

 

If this post helps, then please mark it as 'Accept as Solution'.

Regards,

Sanket Bhagwat

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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