Running Totals

[mikeoshields]

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_nbr	wr_lot	MinDate	MaxDate	DateDiff	Running1	RunningTotal
317967	18315747	5/8/2019	5/9/2019	1	0	0
317967	18315747	5/14/2019	5/14/2019	0	1	1
317967	18315747	5/15/2019	5/15/2019	0	1	1
317967	18315747	5/19/2019	5/21/2019	2	3	3
317967	18315747	5/21/2019	5/23/2019	2	5	5
317968	18315748	5/9/2019	5/9/2019	0	0	5
317968	18315748	5/14/2019	5/14/2019	0	0	5
317968	18315748	5/15/2019	5/15/2019	0	0	5
317968	18315748	5/16/2019	5/20/2019	4	4	9
317968	18315748	5/20/2019	5/20/2019	0	0	9
317969	18315749	5/10/2019	5/10/2019	0	0	9
317969	18315749	5/14/2019	5/14/2019	0	0	9
317969	18315749	5/15/2019	5/15/2019	0	0	9
317969	18315749	5/18/2019	5/18/2019	0	0	9
317969	18315749	5/22/2019	5/23/2019	1	1	10

[Ashish_Mathur]

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

[mikeoshields]

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

[Ashish_Mathur]

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

[mikeoshields · 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])

)

)