Last 12 months over months

[manishjoshi]

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	25	5	25.00%
nov 20	31	6	24.00%
dec 20	35	4	12.90%
jan 21	42	7	20.00%
feb 21	45	3	7.14%
mar 21	53	8	17.78%
apr 21	55	2	3.77%
may 21	59	4	7.27%
jun 21	65	6	10.17%
jul 21	70	5	7.69%

Thanks

Manish

[Anonymous]

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

https://youtu.be/d8Rm7dwM6gc

Watch it carefully till the very end.

[manishjoshi]

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 ) )

Regards,

Manish

[Anonymous]

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]

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

[SanketBhagwat]

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

[manishjoshi]

Hi Sanket,

Thank for your help.

I will change and let you know.

Regards,

Manish

[manishjoshi]

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

[SanketBhagwat]

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

[manishjoshi]

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

Regards,

Manish

[SanketBhagwat]

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

[manishjoshi]

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]

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