Forum Discussion
Previous computation on Dax
Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/7HLKwsbFecvw2MlKExisting = VAR CurrentDate = Data[Month] VAR CurrentProgramTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Program] ) ) VAR TableOnAndBefore = FILTER ( CurrentProgramTable, Data[Month] <= CurrentDate ) RETURN SUMX ( TableOnAndBefore, Data[New] - Data[Removed] )
Community ChampionAnonymous
Let's assume New = N, Removed = R, Existing = E then:
E1 = N1 - R1
E2 = E1 + N2 - R2 >> E2 = N1 - R1 + N2 - R2 >> E2 = ( N1 - R1 ) + ( N2 - R2 )
Acoordingly
E1 = ( N1 - R1 )
E2 = ( N1 - R1 ) + ( N2 - R2 )
E3 = ( N1 - R1 ) + ( N2 - R2 ) + ( N3 - R3 )
E4 = ( N1 - R1 ) + ( N2 - R2 ) + ( N3 - R3 ) + ( N4 - R4 )
and so on...
which can be represented as SUMX ( Table(From 1 to n), Nn - Rn)
We need to do that for each program seperately. This is what the CALCULATE modifier "ALLEXCEPT" does. So for each row we calculate a table that contains only the rows that belong to the same project of that particular row. Then we filter this table keeping the rows that are on or before the current date of that row i.e. from the 1(st) to the n(th) row.
Please let me know if you need any further clarfication.
Cool! Thank you so much for explaining