cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BunnyV Frequent Visitor
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-181194
Feb-181103
Mar-181313
Apr-181289
May-181363
Jun-181317
Jul-181518
Aug-181560
Sep-181474
Oct-181420
Nov-181487
Dec-181526
Jan-191437
Feb-191459
Mar-191650
Apr-191470
May-191699
Jun-191421
Jul-191591
Aug-191549
Sep-191517
Oct-191275
Nov-191417
Dec-191428
Jan-20283

 

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-1811941194
Feb-1811032297
Mar-1813133610
Apr-1812894899
May-1813636262
Jun-1813177579
Jul-1815189097
Aug-18156010657
Sep-18147412131
Oct-18142013551
Nov-18148715038
Dec-18152616564
Jan-19143716807
Feb-19145917163
Mar-19165017500
Apr-19147017681
May-19169918017
Jun-19142118121
Jul-19159118194
Aug-19154918183
Sep-19151718226
Oct-19127518081
Nov-19141718011
Dec-19142817913
Jan-2028316759

 

Example:- 

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

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

 

 Can anyone please help me.

 

Thanks in advance.

BunnyV.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-kelly-msft
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:

 

111.png

 

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:

112.png

For the related .pbix file,pls click here.

 

Hope this would help.

 

Best Regards,

Kelly

View solution in original post

4 REPLIES 4
Super User IV
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
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

 

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

 





Did I answer your question? Mark my post as a solution!
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


BunnyV Frequent Visitor
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.

 

Capture1.PNG

 

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

 

Any help.

Super User IV
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...





Did I answer your question? Mark my post as a solution!
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 v-kelly-msft
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:

 

111.png

 

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:

112.png

For the related .pbix file,pls click here.

 

Hope this would help.

 

Best Regards,

Kelly

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors