Forum Discussion
Calculate Historical value based on value
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 |
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
2 Replies
- selimovd
Most Valuable Professional
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic