topic Re: Last 12 months over months in DAX Commands and Tips

Last 12 months over months

manishjoshi — Tue, 14 Sep 2021 11:46:12 GMT

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.

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

Re: Last 12 months over months

Anonymous — Tue, 14 Sep 2021 12:32:37 GMT

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

Re: Last 12 months over months

manishjoshi — Wed, 15 Sep 2021 00:25:11 GMT

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

Re: Last 12 months over months

Anonymous — Wed, 15 Sep 2021 05:03:35 GMT

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

Re: Last 12 months over months

manishjoshi — Wed, 15 Sep 2021 05:43:56 GMT

Hi Sanket,

Thank for your help.

I will change and let you know.

Regards,

Manish

Re: Last 12 months over months

Anonymous — Wed, 15 Sep 2021 15:35:02 GMT

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

https://youtu.be/d8Rm7dwM6gc

Watch it carefully till the very end.

Re: Last 12 months over months

manishjoshi — Thu, 16 Sep 2021 07:55:00 GMT

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

Re: Last 12 months over months

Anonymous — Thu, 16 Sep 2021 08:10:55 GMT

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

Re: Last 12 months over months

manishjoshi — Thu, 16 Sep 2021 08:20:26 GMT

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

Re: Last 12 months over months

Anonymous — Thu, 16 Sep 2021 08:24:11 GMT

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

Re: Last 12 months over months

manishjoshi — Thu, 16 Sep 2021 08:30:41 GMT

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

Re: Last 12 months over months

manishjoshi — Mon, 20 Sep 2021 07:37:44 GMT

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

Re: Last 12 months over months

Anonymous — Mon, 20 Sep 2021 09:53:27 GMT

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