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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.