Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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