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

[banupriya45]

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

[Jihwan_Kim · Accepted solution]

Hi, 

mine is also August 2021 version.

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

Thank you.

[Jihwan_Kim]

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 

[banupriya45]

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

[Jihwan_Kim]

Hi,

I cannot open your shared file.

Could you please check?

[banupriya45]

Hi,

I am using the August 2021 version 

[Jihwan_Kim · Accepted solution]

Hi, 

mine is also August 2021 version.

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

Thank you.

[banupriya45]

Can you try with the Samplefile link please

[Jihwan_Kim · Accepted solution]

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

[banupriya45]

Thanks alot. It's working perfectly 🙂

[Tanushree_Kapse]

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.