Hi,
I have a table with change documentation values, where only the columns, where the user has changed something in the frontend are stored. The other rows with fact data stay blank.
My problem is, that i need to do a calculation, where all relevant rows need to have a value <> blank to make it work properly. My goal would be to use the value from a previous associated change documentation row, if a relevant field needed in the calculation is blank but i don't know how to do this the right way.
In the Column "Forecast Cost Deviation" i would like to compute from "Current Budget" - "Costs to Date" - "Costs to Complete" in DAX. If there is a blank value in a relevant row, it should use the value from the line <> blank with the most recent date before, where the value in the Fields "GlobalProjectKey", "OrderposKey" and "Cost code" match too.
To substitute the blank values in Power Query or create separate calculated columns would also work for me, if would know, how to do it right.
| GlobalProjectKey | OrderPosKey | Cost Code | Current Budget | Costs to Date | Costs To Complete | Forecast Cost Deviation | Change Date |
A | 1 | 605 | 1000 | 500 | 1200 | -700 | 01.01.2022 |
| A | 1 | 605 | 1000 | 600 | 1400 | -1000 | 05.01.2022 |
| A | 1 | 605 | blank | blank | 1300 | ?????? | 08.02.2022 |
| A | 1 | 605 | blank | blank | 1350 | ?????? | 09.02.2022 |
| A | 1 | 605 | 1100 | 700 | 1000 | -600 | 11.02.2022 |
| A | 1 | 605 | 1100 | 800 | 800 | -500 | 12.02.2022 |
| X | 999 | 999 | 1000 | 1000 | 1000 | -1000 | 05.02.2022 |
| Y | 534 | 999 | 2332 | 2382 | 0 | -50 | 02.01.2021 |
Based on the example data above, the blank values in the "Current Budget" column need to be substituted with 1100 and in "Costs To Date" with 600.
I would highly appreciate your help to find a solution for this problem.
Thank you,
Best regards,
Reinhard
