How to Get Value from Previous Row if Blank (Across Three Measures from Different Tables)

[Yuiitsu]

Hi all,

I’m trying to build a visual in Power BI that shows the following:

Date

HQ Sales (SUM measure from Table A)

EU Sales (SUM measure from Table B)

Budget (SUM measure from Table B)

All tables are linked through a Date table, and the Date field is used.

Requirement: If a measure returns blank for a date, I would like it to display the most recent non-blank value from previous dates.

In a seperate thread I have the solution for 2 different tables but now I wish to add in a 3rd table.

Using this link to the sample BI (File Link  ) here, could anyone help to modify to suit this new requirement?

[v-venuppu]

Hi @Yuiitsu ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu · Accepted solution]

Hi @Yuiitsu ,

Please find the attached PBIX file for your reference.Done few changes in the file, hope it will help.

Thank you.

[v-venuppu]

Hi @Yuiitsu ,

Thank you for the clarification.

Can you please help me with the exact output you are expecting like what are the values that should be filled in the blanks, so that will try to implement the same thing.

[v-venuppu]

Hi @Yuiitsu ,

Below is your expected output given in your previous post:

You can see the same output from my attached file:

Please let us know if you need any further assistance.

Thank you.

[Yuiitsu]

Hi

I think you misunderstood my message. Here is what I wrote:

"Below is how it looks like without any measures to fill up the blanks.

I only want the blanks filled up in these dates ......"

The sceenshot below is showing you that without any measure, this is how it looks like.

What I expect is the blanks in the screenshot to be filled up.

[v-venuppu]

Hi @Yuiitsu ,

I have tried replicating the same output, done few changes in measures.

Please go through the attached PBIX file for your reference.

Thank you.

[Yuiitsu]

This is the output?

[v-venuppu]

Hi @Yuiitsu ,

I have done some changes in the sample file.

1.Created a helper table called LastNonBlankDates.

2.Then created three new measures.(HQ Sales Blanks Filled_New,EU Sales Blanks Filled_New,Budget Blanks Filled_New).I hope this helps..!!

Please go through the attached PBIX file for your reference.

Thank you.

[Yuiitsu]

Hi @v-venuppu 

Thanks, but the result is still not what I am looking for.

Below is how it looks like without any measures to fill up the blanks.

I only want the blanks filled up in these dates but using your measures i will have blanks filled up in every single date inside the date key.

Your measure result:

[v-venuppu]

Hi @Yuiitsu ,

I have tried replicating the scenario by using sample data.Please go through the attached PBIX file for your reference.

Thank you.

[Yuiitsu]

Hi @v-venuppu 

Your solution works but in my case i am using measures not the exact column inside the data.

If you look at my sample, i have 3 measures and i want to use them instead of the exact column is it possible?

Also in my data, EU, BU sales and budget does not exist in the same data set. They are 3 different tables.

[v-venuppu]

Hi @Yuiitsu ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @Yuiitsu ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ashish_Mathur @ThxAlot @burakkaragoz @GrowthNatives for the prompt response.

I wanted to check if you had the opportunity to review the information provided by @Ashish_Mathur and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[Yuiitsu]

Doesnt work at this moment I am still trying to fit it into my actual data file😣

[Ashish_Mathur]

Hi,

Reduce the size of your Calendar table by using this formula

Date = 

ADDCOLUMNS (

CALENDAR (DATE (2025, 4, 1), DATE (2025, 07, 31)),

"Year", YEAR([Date]),
"Year-Month", FORMAT ( [Date], "yyyy-mmmm" ),
"MonthNumber", MONTH([Date])
)

Revised your measure to

EU Sales Blanks Filled = if((ISBLANK([HQ Sales])&&ISBLANK([EU Sales])&&ISBLANK([Budget])),BLANK(),CALCULATE([EU Sales],CALCULATETABLE(LASTNONBLANK('Date'[Date],CALCULATE([EU Sales])),DATESBETWEEN('Date'[Date],MINX(ALL('Date'),'Date'[Date]),MAX('Date'[Date])))))

HQ Sales Blanks Filled = if((ISBLANK([HQ Sales])&&ISBLANK([EU Sales])&&ISBLANK([Budget])),BLANK(),CALCULATE([HQ Sales],CALCULATETABLE(LASTNONBLANK('Date'[Date],CALCULATE([HQ Sales])),DATESBETWEEN('Date'[Date],MINX(ALL('Date'),'Date'[Date]),MAX('Date'[Date])))))

Hope this helps.

[ThxAlot]

Easy enough,

[Yuiitsu]

Hi @ThxAlot 

The first 2 columns must be all filled from previous row as well.

As you can see from my screenshot below that without the budget column they work well.

But once the budget is added in the blanks appear

Apologise for not explaining it detailed enough earlier.

[burakkaragoz]

Hi @Yuiitsu ,

That DAX code has some issues and won't work properly for what you're trying to do. The logic is flawed because it's using MAX on what should be SUM measures, and the filtering isn't quite right.

Here's the correct approach for each of your three measures:

HQ Sales (Forward Fill) = 
VAR CurrentDate = MAX('Date'[Date])
VAR CurrentValue = [HQ Sales Original]
VAR LastNonBlankValue = 
    CALCULATE(
        [HQ Sales Original],
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] <= CurrentDate && 
            NOT(ISBLANK([HQ Sales Original]))
        ),
        LASTDATE('Date'[Date])
    )
RETURN
IF(ISBLANK(CurrentValue), LastNonBlankValue, CurrentValue)

EU Sales (Forward Fill) = 
VAR CurrentDate = MAX('Date'[Date])
VAR CurrentValue = [EU Sales Original]
VAR LastNonBlankValue = 
    CALCULATE(
        [EU Sales Original],
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] <= CurrentDate && 
            NOT(ISBLANK([EU Sales Original]))
        ),
        LASTDATE('Date'[Date])
    )
RETURN
IF(ISBLANK(CurrentValue), LastNonBlankValue, CurrentValue)

Budget (Forward Fill) = 
VAR CurrentDate = MAX('Date'[Date])
VAR CurrentValue = [Budget Original]
VAR LastNonBlankValue = 
    CALCULATE(
        [Budget Original],
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] <= CurrentDate && 
            NOT(ISBLANK([Budget Original]))
        ),
        LASTDATE('Date'[Date])
    )
RETURN
IF(ISBLANK(CurrentValue), LastNonBlankValue, CurrentValue)

Key differences from the suggested answer:

• Uses your actual SUM measures instead of MAX on base columns
• Properly handles the forward-fill logic with LASTDATE
• Checks if current value is blank first, only then looks backward

Make sure your original measures are named something like:

• [HQ Sales Original] for your Table A SUM
• [EU Sales Original] for your Table B SUM
• [Budget Original] for your Table B budget SUM

This approach works consistently across multiple tables and handles the date relationships properly through your Date table.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[Yuiitsu]

Thank you @burakkaragoz ! 

Just to double confirm you are requesting me to change my following measure

to :

• [HQ Sales Original] for your Table A SUM
• [EU Sales Original] for your Table B SUM
• [Budget Original] for your Table B budget SUM

Would it be possible for you to work the magic in my sample file and share it with me so I can understand it better?

[GrowthNatives]

Hi @Yuiitsu , 
You can create measures with forward-fill logic. This will ensure that blanks are replaced with the most recent non-blank value from earlier dates.

Here is the code you can use for creating measures & similarly for EU Sales and Budget

DAX 
HQ Sales (Forward Fill) =
VAR CurrentDate = MAX('Date'[Date])
RETURN
CALCULATE(
    CALCULATE(MAX('Table A'[HQ Sales])),
    FILTER(
        ALL('Date'),
        'Date'[Date] <= CurrentDate &&
        NOT(ISBLANK(CALCULATE(MAX('Table A'[HQ Sales]))))
    )
)

 
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]