Calculate Historical value based on value

[Anonymous]

Need help with calculating historcal data.

I have data for Column A-D. But want to Calculate (with DAX) E. Col D i så always the act. value. And Col E can be reverserd calculated.

Ex.

Col	A	B	C	D	E
Date	IN	Out	Net	Act	Real Act
2021-01-01	1000	800	200	15000	9942
2021-01-02	1500	900	600	15000	10142
2021-01-03	1544	1000	544	15000	10742
2021-01-04	1251	500	751	15000	11286
2021-01-05	2155	800	1355	15000	12037
2021-01-06	1001	450	551	15000	13392
2021-01-07	1000	555	445	15000	13943
2021-01-08	1300	888	412	15000	14388
2021-01-09	1000	800	200	15000	14800
2021-01-10	1500	900	600	15000	15000

[selimovd · Accepted solution]

Hey @Anonymous ,

you can do that with a calculated column.

Try the following formula:

RealAct = 
VAR vDateRow = myTable[Date]
VAR vLastDate = MAX( myTable[Date] )
VAR vAct = myTable[Act]
VAR vFilterTable = FILTER( myTable, myTable[Date] >= vDateRow && myTable[Date] < vLastDate )
RETURN
    vAct - SUMX( vFilterTable, myTable[Net] )

For me that works:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Nice! Thx 😄