cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
waldnerr
Helper I
Helper I

Replace empty value with value from previous change documentation row value

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.

 

 

GlobalProjectKeyOrderPosKeyCost CodeCurrent BudgetCosts to DateCosts To CompleteForecast Cost DeviationChange Date

A

160510005001200-70001.01.2022
A160510006001400-100005.01.2022
A1605blankblank1300??????08.02.2022
A1605blankblank1350??????09.02.2022
A160511007001000-60011.02.2022
A16051100800800-50012.02.2022
X999999100010001000-100005.02.2022
Y534999233223820-5002.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

 

 

 

 

1 ACCEPTED SOLUTION
mohammedadnant
Impactful Individual
Impactful Individual

hi @waldnerr 

 

You can do this using Power Query Editor

Sort your data based on your criteria and then select the Current Budget --> on ribbon --> Transform --> Fill --> Down.

 

This will fill you with the recent data in the blank cell.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

View solution in original post

1 REPLY 1
mohammedadnant
Impactful Individual
Impactful Individual

hi @waldnerr 

 

You can do this using Power Query Editor

Sort your data based on your criteria and then select the Current Budget --> on ribbon --> Transform --> Fill --> Down.

 

This will fill you with the recent data in the blank cell.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.