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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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