Previous computation on Dax

[krixtsup3r]

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!

[tamerj1 · Accepted solution]

@krixtsup3r 
Here is a sample file with the solution https://www.dropbox.com/t/7HLKwsbFecvw2MlK

Existing = 
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] )

[krixtsup3r]

Thank you! This works, but can you explain to me the formula? Also what if I add more columns within it what should I change? 

[tamerj1]

Hi @krixtsup3r 

Is this table visual or source data? Are New and Removed measures? What is the dax code?

[krixtsup3r]

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]

@krixtsup3r 
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.

[krixtsup3r]

Cool! Thank you so much for explaining

[tamerj1]

@krixtsup3r 
Thank you. Can you please provide the same sample data as copy/paste so I can prepare a sample file for you?

[krixtsup3r]

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

[ssysanny]

see this one. too complicated. haha

https://1drv.ms/x/s!AqYSh-5UaI9Wg19Aub-s5I10So0a