cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX - Calculate sum of last 12 months from each month

Hello All,

I am new to powerbi and DAX.

I have a sample data set and it is as below table.

MONTHYEAR            Sales

 Jan-18 1194 Feb-18 1103 Mar-18 1313 Apr-18 1289 May-18 1363 Jun-18 1317 Jul-18 1518 Aug-18 1560 Sep-18 1474 Oct-18 1420 Nov-18 1487 Dec-18 1526 Jan-19 1437 Feb-19 1459 Mar-19 1650 Apr-19 1470 May-19 1699 Jun-19 1421 Jul-19 1591 Aug-19 1549 Sep-19 1517 Oct-19 1275 Nov-19 1417 Dec-19 1428 Jan-20 283

I would like to create a calculated column (NOT MEASURE) which gives me sum of last 12 months from each month.

The expecting output is something like,

MONTHYEAR |                       Sales |                        Sales of Last 12 Months from Each month

 Jan-18 1194 1194 Feb-18 1103 2297 Mar-18 1313 3610 Apr-18 1289 4899 May-18 1363 6262 Jun-18 1317 7579 Jul-18 1518 9097 Aug-18 1560 10657 Sep-18 1474 12131 Oct-18 1420 13551 Nov-18 1487 15038 Dec-18 1526 16564 Jan-19 1437 16807 Feb-19 1459 17163 Mar-19 1650 17500 Apr-19 1470 17681 May-19 1699 18017 Jun-19 1421 18121 Jul-19 1591 18194 Aug-19 1549 18183 Sep-19 1517 18226 Oct-19 1275 18081 Nov-19 1417 18011 Dec-19 1428 17913 Jan-20 283 16759

Example:-

For Jan-20 - Sum of (Feb-19 to Jan-20) = 16759

For Dec-19 - Sum of (Jan-19 to Dec-19) = 17913

BunnyV.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: DAX - Calculate sum of last 12 months from each month

Hi @BunnyV ,

First you should create a calendar table using “calendar()” function which is as shown below：

Then create a calculated column using following dax expression:

``````YTDtotal =
VAR a='sample data'[MONTHYEAR]
var lastyear= YEAR('sample data'[MONTHYEAR])-1
var lastmonth=IF(MONTH('sample data'[MONTHYEAR])>=12,MONTH('sample data'[MONTHYEAR])-12,MONTH('sample data'[MONTHYEAR])+1)
var lastday=DAY('sample data'[MONTHYEAR])
var last=DATE(lastyear,lastmonth,lastday)
Return
CALCULATE(SUM('sample data'[  Sales]),DATESBETWEEN('sample data'[MONTHYEAR],last,'sample data'[MONTHYEAR]))``````

Finally,you will see:

Hope this would help.

Best Regards,

Kelly

4 REPLIES 4
Super User IV

## Re: DAX - Calculate sum of last 12 months from each month

Not Sure if you have a date column. In case you create a date column based on Month Name and join it with calendar, it can be done pretty easily in the column too. I tried same for MTD and it worked.

``CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date])) ``

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Frequent Visitor

## Re: DAX - Calculate sum of last 12 months from each month

thanks for the reply @amitchandak .

I have created date column using MonthYear column by parsing.

Now based on that i did created calender table and created relation with my data table.

But as you mentioend when i tried to mark it as a date table it is not letting me to do it, because the dates are having gaps in between.

But still i tried what you have suggest the dax formula.

I see that its not working.

I even tried the measure, but both giving the same result.

Any help.

Super User IV

## Re: DAX - Calculate sum of last 12 months from each month

How you have created your calendar table. Hope not user Auto Calendar. You used calendar, the way it has been given in link.

If possible please share a sample pbix file after removing sensitive information.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Microsoft

## Re: DAX - Calculate sum of last 12 months from each month

Hi @BunnyV ,

First you should create a calendar table using “calendar()” function which is as shown below：

Then create a calculated column using following dax expression:

``````YTDtotal =
VAR a='sample data'[MONTHYEAR]
var lastyear= YEAR('sample data'[MONTHYEAR])-1
var lastmonth=IF(MONTH('sample data'[MONTHYEAR])>=12,MONTH('sample data'[MONTHYEAR])-12,MONTH('sample data'[MONTHYEAR])+1)
var lastday=DAY('sample data'[MONTHYEAR])
var last=DATE(lastyear,lastmonth,lastday)
Return
CALCULATE(SUM('sample data'[  Sales]),DATESBETWEEN('sample data'[MONTHYEAR],last,'sample data'[MONTHYEAR]))``````

Finally,you will see:

Hope this would help.

Best Regards,

Kelly

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!