Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi,
mine is also August 2021 version.
What I meant was, I could not access to your link.
Thank you.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 ()
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
Thanks
Hi,
I cannot open your shared file.
Could you please check?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am using the August 2021 version
Hi,
mine is also August 2021 version.
What I meant was, I could not access to your link.
Thank you.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 ()
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks alot. It's working perfectly 🙂
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |