Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Laurix
Frequent Visitor

Last 12 months rolling sum of measure

Hi everybody!

 

I have a problem which should be simple, however I don't know why I cannot get the correct result.

I have a table called 'Reporting' with two columns: [Report date] and [Accumulated value]. The [Accumulated value] column is in fact a rolling sum  which resets every January.

The table with the desired output (last two columns) is

 

Report Date            Accumulated value          

Monthly value

(current month - previous month)

Last 12 months sum of Monthly values (including current month)
1-Jan-2019100100 
1-Feb-201915050 
1-Mar-201918030 
1-Apr-201922040 
1-May-201927050 
1-Jun-201930030 
1-Jul-201931010 
1-Aug-201933020 
1-Sep-201937040 
1-Oct-201940030 
1-Nov-201946060 
1-Dec-201950040 
1-Jan-20205050450
1-Feb-202011060460
1-Mar-202015040470
1-Apr-202017020450
1-May-202020030430
1-Jun-202022020420
1-Jul-202027050460
1-Aug-202029020460
1-Sep-202037080500
1-Oct-202040030500
1-Nov-202044040480
1-Dec-202049050490

 

For the [Monthly Value] measure I get the correct results using the formula

Monthly Value = if(MONTH(MAX(Reporting[Report Date]))=1,

SUM(Reporting[Accumulated value]),

SUM(Reporting[Accumulated value])-CALCULATE(sum(Reporting[Accumulated value]),PREVIOUSMONTH('Calendar'[Date])))

 

For the [Last 12 months sum] measure I'm trying with the formula

Last 12 months sum = CALCULATE([Monthly Value] ,
DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]),-12,MONTH))

however instead of the needed value, I'm getting the rolling sum of the [Accumulated value] column (100, 250, 430 etc.).

 

What am I doing wrong?

 

Any help would be highly appreciated.

 

Thank you!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Laurix , Create measure like

 

Current Value =
var _max = EOMONTH(max('Date'[Date]),-1)
var _min1  = EOMONTH(max('Date'[Date]),-2) +1
var _min = if( YEAR(_min1) <> YEAR(max('Date'[Date])) ,_max+1, _min1)
return
CALCULATE(SUM(Data[Accumulated value]), DATESMTD('Date'[Date])) -CALCULATE(SUM(Data[Accumulated value]),DATESBETWEEN('Date'[Date],_min, _max))
 
rolling 12 =
CALCULATE( sumx(VALUES('Date'[Month year sort]), [Current Value]), WINDOW(-11,REL,0,REL,ALL('Date'[Month year sort],'Date'[Month Year]),ORDERBY('Date'[Month year sort])))
 
or
 
rolling 12 DP =
CALCULATE( sumx(VALUES('Date'[Month year sort]), [Current Value]), DATESINPERIOD('Date'[Date], max('Date'[Date]), -12, MONTH))
 
 
amitchandak_0-1679461994615.png

 

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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 :radacad sqlbi My Video Series Appreciate your Kudos.

View solution in original post

2 REPLIES 2
Laurix
Frequent Visitor

Thank you, @amitchandak !

 

My formula for the Current Value was also returning the correct values, however the SUMX for the rolling sum for the last 12 months did the trick (I knew I needed a SUMX there, it was that simple...).

All the best!

amitchandak
Super User
Super User

@Laurix , Create measure like

 

Current Value =
var _max = EOMONTH(max('Date'[Date]),-1)
var _min1  = EOMONTH(max('Date'[Date]),-2) +1
var _min = if( YEAR(_min1) <> YEAR(max('Date'[Date])) ,_max+1, _min1)
return
CALCULATE(SUM(Data[Accumulated value]), DATESMTD('Date'[Date])) -CALCULATE(SUM(Data[Accumulated value]),DATESBETWEEN('Date'[Date],_min, _max))
 
rolling 12 =
CALCULATE( sumx(VALUES('Date'[Month year sort]), [Current Value]), WINDOW(-11,REL,0,REL,ALL('Date'[Month year sort],'Date'[Month Year]),ORDERBY('Date'[Month year sort])))
 
or
 
rolling 12 DP =
CALCULATE( sumx(VALUES('Date'[Month year sort]), [Current Value]), DATESINPERIOD('Date'[Date], max('Date'[Date]), -12, MONTH))
 
 
amitchandak_0-1679461994615.png

 

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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 :radacad sqlbi My Video Series Appreciate your Kudos.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.