cancel
Showing results for
Did you mean:
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 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

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.

Frequent Visitor

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

Frequent Visitor

Hi Sanket,

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

Super User

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

Use the below steps;

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

3)MoM% = ([SumSales]/[LastMonth]-1)/100.

Please let me know if that helps.

Regards,
Sanket Bhagwat
Frequent Visitor

Hi Sanket,

I will change and let you know.

Regards,

Manish

Frequent Visitor

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

Super User

You will need to modify the measure which I sent.

Last12months=CALCULATE(SUM('Table'[Sales]),

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

Regards,

Sanket Bhagwat

Frequent Visitor

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

Super User

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

Frequent Visitor

Hi Sanket,

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

Super User

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors