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 ChampionHi Anonymous
Is this table visual or source data? Are New and Removed measures? What is the dax code?
Hi tamerj1, the columns that doesn't have fill are from tables, while the yellow is I am expecting to be a calculated column
- tamerj1
Anonymous
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
- tamerj1
Anonymous
Thank you. Can you please provide the same sample data as copy/paste so I can prepare a sample file for you?Here tamerj1
Month Year Program New Removed Existing 8/1/2019 2019 Project 1 3 1 2 9/1/2019 2019 Project 1 4 5 1 10/1/2019 2019 Project 1 67 4 64 8/1/2019 2019 Project X 57 5 52 9/1/2019 2019 Project X 118 57 113 10/1/2019 2019 Project X 151 58 206 8/1/2019 2019 Project XBZ 29 1 28 9/1/2019 2019 Project XBZ 264 5 287 10/1/2019 2019 Project XBZ 53 7 333 11/1/2019 2019 Project XBZ 6 327