Forum Discussion
DAX - How calculate a Running Total 2 from previous Running Total 1
After various tests, I found a solution. :smileyvery-happy:
Running Tot2 = SUMX(FILTER(ALLSELECTED(DimTime[Date]),DimTime[Date] <= MAX((DimTime[Date]))),[Running Tot1])
After various tests, I found a solution. :smileyvery-happy:
Running Tot2 = SUMX(FILTER(ALLSELECTED(DimTime[Date]),DimTime[Date] <= MAX((DimTime[Date]))),[Running Tot1])
Skilled SharerPerformance-wise you could find that slow because it's recursing through PM Sales.
E.g. 2/24/2015 value of 37 is:
2/3/2015 * 4 = 4 * 4 = 16 (where 4 is the total number of rows)
2/10/2015 * 3 = 4 * 3 = 12 (where 3 is the total number of rows less 1)
2/17/2015 * 2 = 4 * 2 = 8 (where 2 is the total number of rows less 2)
2/24/2015 * 1 = 1 * 1 = 1 (where 1 is the total number of rows less 3)
=16+12+8+1
=37
Which doesn't sound so bad, but it isn't doing it as a multiplication. SUMX is an iterative function, so the number in bold is the number of times it's reading the value and adding it into the total. So the number of reads is exponential depending on the number of rows. (1000 rows = 500,500 reads). It's a sum of the first n natural numbers where n is the number of rows problem.
I don't have a better solution though...you've reached the limits of my DAX knowledge. So I'm hoping this is at least performant enough to get you by.