cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Anonymous
Not applicable

Nice! Thx 😄

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors