Forum Discussion
Previous computation on Dax
Hi guys, just wanna ask on how can I compute from previous which will be based multiple column. See my expected out below and the computation that I did on Excel
Thank you so much!
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] )
9 Replies
Hi 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
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.
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] )