DAX - Calculate sum of last 12 months from each month

[BunnyV]

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

 Can anyone please help me.

Thanks in advance.

BunnyV.

[Anonymous]

Hello @v-kelly-msft ,

Thank you for this!

Just a quick one:

How to calculate the same if the Dates are the end of each month? I tried above DAX but not getting the ytd total as expected (works in the measure but when date filter is applied, the measure goes blank)

Sharing the sample data for reference:

Date	ID	Revenue	Revenue YTD (Column)	Revenue_Ytd (Measure)
31/07/2018	32466	5495.42	5495.42
31/08/2018	32466	2815.07	2815.07
30/09/2018	32466	2878.24	2878.24
31/10/2018	32466	2795.92	2795.92
30/11/2018	32466	2818.15	2818.15
31/12/2018	32466	2664.91	2664.91
31/01/2019	32466	2445.28	2445.28
28/02/2019	32466	2262.11	2262.11
31/03/2019	32466	2067.89	2067.89
30/04/2019	32466	2314.28	2314.28	28557.27
31/05/2019	32466	2261.89	2261.89	30819.16
30/06/2019	32466	2342.66	2342.66	33161.82
31/07/2019	32466	2260.47	2260.47	29926.87
31/08/2019	32466	2281.32	2281.32	29393.12
30/09/2019	32466	2305.82	2305.82	28820.7
31/10/2019	32466	2260.63	2260.63	28285.41
30/11/2019	32466	2333.88	2333.88	27801.14
31/12/2019	32466	2290.43	2290.43	27426.66
31/01/2020	32466	2354.21	2354.21	27335.59
28/02/2020	32466	2370.57	2370.57	27444.05
31/03/2020	32466	2186.49	2186.49	27562.65
30/04/2020	32466	2143.56	2143.56	27391.93
31/05/2020	32466	1987.85	1987.85	27117.89
30/06/2020	32466	2092.42	2092.42	26867.65
31/07/2020	32466	2036.24	2036.24	26643.42
31/08/2020	32466	2070.13	2070.13	26432.23
30/09/2020	32466	2090.24	2090.24	26216.65
31/10/2020	32466	1992.35	1992.35	25948.37
30/11/2020	32466	2046.59	2046.59	25661.08
31/12/2020	32466	2018.55	2018.55	25389.2
31/01/2021	32466	2076.07	2076.07	25111.06
28/02/2021	32466	2089.05	2089.05	24829.54
31/03/2021	32466	1870.88	1870.88	24513.93
1/04/2021	32466	2077.57	2077.57	24447.94

The DAX I used for measure is:

Revenue_Ytd (Measure) =

VAR LastNotBlankDate =

CALCULATE (

LASTDATE ( 'Client Details'[Date] ),

FILTER (

ALLSELECTED ('Client Details'),

LASTNONBLANK ( 'Client Details'[Date], 'Client Details'[Revenue.1] )))

VAR MinDate =

CALCULATE ( FIRSTDATE ( 'Client Details'[Date] ), ALLSELECTED ( 'Client Details' ) )

VAR Revenue_of_last_year =

IF (

MAX ( 'Client Details'[Date] ) <= LastNotBlankDate,

CALCULATE (

SUM ( 'Client Details'[Revenue.1] ),

DATESINPERIOD ('Client Details'[Date], LASTDATE ('Client Details'[Date]), -1, YEAR )

),

CALCULATE (

SUM ( 'Client Details'[Revenue.1] ),

DATESINPERIOD ('Client Details'[Date], LastNotBlankDate, -1, YEAR )

),

VAR CurrentRowDate =

MAX ( 'Client Details'[Date])

VAR Revenue_of_last_year_2 =

IF (

DATEDIFF ( MinDate, CurrentRowDate, MONTH ) + 1 < 12,

BLANK (),

Revenue_of_last_year)

RETURN

IF (

DATEDIFF ( MinDate, MAX ('Client Details'[Date] ), MONTH ) + 1 < 12,

BLANK (),

Revenue_of_last_year

)

Any help would be greatly appreciated!

Thanks!

[v-kelly-msft]

Hi  @Anonymous ,

Pls create a new thread with your requirment which will be given priority to follow.🙂

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft · Accepted solution]

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:

For the related .pbix file,pls click here.

Hope this would help.

Best Regards,

Kelly

[plittleton1988]

Hi, Kelly. Should the calculated column be added to the calendar table created in the previous step?

[amitchandak]

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

[BunnyV]

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.

[amitchandak]

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