topic I am trying to calculate a running total over previous 12 months, for each month of year in DAX Commands and Tips

I am trying to calculate a running total over previous 12 months, for each month of year

banupriya45 — Thu, 19 Aug 2021 08:50:03 GMT

I am trying to calculate the previous 12 months running total for each month. Example: If I select year 2020 and month Dec in the slicer. The table should display from Jan 2020 to Dec 2020 and Dec 2020 running total should be the sum of Jan 2020 to Dec 2020. If I select the year 2020 and the Month Nov then the table should display data from Dec 2019 to Nov 2020 and Nov 2020 running total should be the sum of Dec 2019 to Nov 2020.

Year	Month	Values	Running Total of previous 12 months for each months
2019	1	5
2019	2	6
2019	3	1
2019	4	2
2019	5	3
2019	6	4
2019	7	1
2019	8	5
2019	9	6
2019	10	1
2019	11	2
2019	12	3
2020	1	1	35	Running total from Feb-2019 to Jan-2020
2020	2	2	31	Running total from Mar-2019 to Feb-2020
2020	3	3	33	Running total from Apr-2019 to Mar-2020
2020	4	4	35	Running total from May-2019 to Apr-2020
2020	5	1	33	Running total from Jun-2019 to May-2020
2020	6	5	34	Running total from Jul-2019 to Jun-2020
2020	7	6	39	Running total from Aug-2019 to Jul-2020
2020	8	1	35	Running total from Sep-2019 to Aug-2020
2020	9	2	31	Running total from Oct-2019 to Sep-2020
2020	10	3	33	Running total from Nov-2019 to Oct-2020
2020	11	2	33	Running total from Dec-2019 to Nov-2020
2020	12	1	31	Running total from Jan-2020 to Dec-2020

Re: I am trying to calculate a running total over previous 12 months, for each month of year

Tanushree_Kapse — Thu, 19 Aug 2021 11:44:23 GMT

Hi banupriya45 ,

I would suggest you to first create a dates column and then

Use this measure for Running Total:

Date= DATE(YEAR(Year), MONTH(Month), 1)
Running Total = CALCULATE(SUM(Values), DATESINPERIOD(Date, LASTDATE(Date), -12, MONTH))

Mark this as a solution if I answered your question, Kudos are always appreciated.

Thanks.

Re: I am trying to calculate a running total over previous 12 months, for each month of year

Jihwan_Kim — Thu, 19 Aug 2021 12:18:00 GMT

Cumulate previous 12 months : =
VAR _latestyear =
CALCULATE ( MAX ( Data[Year] ), ALL () )
VAR _latestmonth =
CALCULATE ( MAX ( Data[Month] ), ALL () )
VAR _yearmonthoffsetnumber_current =
SELECTEDVALUE ( Data[Year], _latestyear ) * 12
+ SELECTEDVALUE ( Data[Month], _latestmonth )
VAR _period_12_months =
FILTER (
ALL ( Data ),
Data[Year] * 12 + Data[Month] <= _yearmonthoffsetnumber_current
&& Data[Year] * 12 + Data[Month] >= _yearmonthoffsetnumber_current - 11
)
RETURN
CALCULATE ( SUM ( Data[Values] ), _period_12_months )

Link to the sample pbix file

Re: I am trying to calculate a running total over previous 12 months, for each month of year

banupriya45 — Thu, 19 Aug 2021 13:25:28 GMT

Hi Jihwan_Kim

Thanks for sharing the file. I have tried the same measure on my power bi file. It's displaying unexpected results. Can you please create the measure in my sample file. I have attached here.

Onedrive

Thanks

Re: I am trying to calculate a running total over previous 12 months, for each month of year

Jihwan_Kim — Thu, 19 Aug 2021 13:51:15 GMT

Hi,

I cannot open your shared file.

Could you please check?

Re: I am trying to calculate a running total over previous 12 months, for each month of year

banupriya45 — Thu, 19 Aug 2021 13:57:14 GMT

Hi,

I am using the August 2021 version

Re: I am trying to calculate a running total over previous 12 months, for each month of year

Jihwan_Kim — Thu, 19 Aug 2021 14:01:02 GMT

Hi,

mine is also August 2021 version.

What I meant was, I could not access to your link.

Thank you.

Re: I am trying to calculate a running total over previous 12 months, for each month of year

banupriya45 — Thu, 19 Aug 2021 14:18:53 GMT

Can you try with the Samplefile link please

Re: I am trying to calculate a running total over previous 12 months, for each month of year

Jihwan_Kim — Thu, 19 Aug 2021 15:00:44 GMT

https://www.dropbox.com/s/xgf8cztetxl562t/Sample.pbix?dl=0

Cumulate previous 12 months : =
VAR _slicerselect =
SELECTEDVALUE ( IndepnedentCalendartable[Year] ) * 12
+ SELECTEDVALUE ( IndepnedentCalendartable[Month] )
VAR _latestyear =
CALCULATE ( MAX ( 'Date'[Year] ), ALL () )
VAR _latestmonth =
CALCULATE ( MAX ( 'Date'[Month] ), ALL () )
VAR _yearmonthoffsetnumber_current =
SELECTEDVALUE ( 'Date'[Year], _latestyear ) * 12
+ SELECTEDVALUE ( 'Date'[Month], _latestmonth )
VAR _period_12_months =
FILTER (
ALL ( 'Date' ),
'Date'[Year] * 12 + 'Date'[Month] <= _yearmonthoffsetnumber_current
&& 'Date'[Year] * 12 + 'Date'[Month] > _yearmonthoffsetnumber_current - 12
)
RETURN
IF (
_yearmonthoffsetnumber_current <= _slicerselect
&& _yearmonthoffsetnumber_current >= _slicerselect - 11,
CALCULATE ( SUM ( 'Values'[Values] ), _period_12_months ),
BLANK ()
)

Re: I am trying to calculate a running total over previous 12 months, for each month of year

banupriya45 — Thu, 19 Aug 2021 16:38:27 GMT

Thanks alot. It's working perfectly 🙂