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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mikeoshields
Helper I
Helper I

Running Totals

I know there are a plethora of running total posts but I haven't found an answer to my specific issue.  My running totals need to be based on the DateDiff field which is a measure.  Running1 is a cumulative sum and resets on the wr_lot number.  RunningTotal has no breaks...just continues the cumulative sum. The table name is wr_route. My question is what is the code to get Running1 and RunningTotal. 

 

 

wr_nbrwr_lotMinDateMaxDateDateDiffRunning1RunningTotal
317967183157475/8/20195/9/2019100
317967183157475/14/20195/14/2019011
317967183157475/15/20195/15/2019011
317967183157475/19/20195/21/2019233
317967183157475/21/20195/23/2019255
317968183157485/9/20195/9/2019005
317968183157485/14/20195/14/2019005
317968183157485/15/20195/15/2019005
317968183157485/16/20195/20/2019449
317968183157485/20/20195/20/2019009
317969183157495/10/20195/10/2019009
317969183157495/14/20195/14/2019009
317969183157495/15/20195/15/2019009
317969183157495/18/20195/18/2019009
317969183157495/22/20195/23/20191110
1 ACCEPTED SOLUTION

Actually I found another solution that's a little simpler:

 

Running1 = CALCULATE(
SUM(wr_route[DateDiff),
FILTER(
ALLSELECTED(wr_route),
wr_route[wr_lot]=MAX(wr_route[wr_lot]) &&
wr_route[MaxDate]<=MAX(wr_route[MaxDate])
)
)

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

This measure works for Running1

Running1 = SUMX(SUMMARIZE(CALCULATETABLE(wr_route,DATESBETWEEN(wr_route[MinDate],MINX(ALL(wr_route[MinDate]),wr_route[MinDate]),MAX(wr_route[MinDate])),DATESBETWEEN(wr_route[MaxDate],MINX(ALL(wr_route[MaxDate]),wr_route[MaxDate]),MAX(wr_route[MaxDate]))),wr_route[MinDate],wr_route[MaxDate],"ABCD",[Datediff]),[ABCD])

Someone else will help you with the second measure

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for the quick response.  Something I should have mentioned is that MinDate and MaxDate are measures not columns so the error I get is "Column 'MinDate' in table 'wr_route' cannot be found or may not be used in this expression".  The fact that these are measures is my biggest issue.  Thanks

You are welcome.  You might as well share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Actually I found another solution that's a little simpler:

 

Running1 = CALCULATE(
SUM(wr_route[DateDiff),
FILTER(
ALLSELECTED(wr_route),
wr_route[wr_lot]=MAX(wr_route[wr_lot]) &&
wr_route[MaxDate]<=MAX(wr_route[MaxDate])
)
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.