How to Get Value from Previous Row if Blank (Across Two 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)

Both tables are linked through a Date table, and the Date field is used as the 1st column in the example below.

Here is my challenge:
The measures for HQ Sales and EU Sales return values on different dates. On some dates, either 1 shows a blank.

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

I have searched the forum and found some similar questions, but unfortunately, the suggested solutions (e.g. using EARLIER,

ISBLANK, or MAXX) does not worked in my case — probably because I could not make it fit into mine.

Is there a DAX approach to accomplish this?

Any help or direction is appreciated. Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Yuiitsu]

Hi @Ashish_Mathur 

Your solution has helped me very much! Once again thank you for your support.

I have another question related to this, if I want to add a new column how could I do this?

I have put a new link to the updated file, hope you can assist this as well.

Updated file link 

[Ashish_Mathur]

You are welcome.  Start a new thread.

[Yuiitsu]

I have created a new thread New Thread here 

[OwenAuger]

Hi @Yuiitsu 

One example of how you could write this, assuming HQ Sales is your existing measure:

HQ Sales Blanks Filled = 
COALESCE (
    [HQ Sales],
    LASTNONBLANKVALUE (
        'Date'[Date] <= MAX ( 'Date'[Date] ),
        [HQ Sales]
    )
)

You may want to add checks to ensure that this is calculated only in the context of a single date.

Does something like this work for you?

[Yuiitsu]

Hi Owen

Thanks for taking the time to share a solution!

I tried implementing it, but unfortunately, I encountered an error. It didn’t seem to work as expected in my setup.

To help clarify the issue, I’ve created a simplified version of my model along with sample data and uploaded it here: 

Would really appreciate if you could take a look and see what might be going wrong based on this file.

Thanks again for your help!

[OwenAuger]

Thanks for that 🙂
The immediate reason for the error is that because of the way I had written the measure, it is being evaluated for every date out to 2050 (the extent of the 'Date' table).

Before suggesting a solution, can I just clarify the requirements:

1. Do you want to "fill in the blanks" for just Dates that are already visible on the visual, in other words, just for Dates that show values for HQ Sales or EU Sales? (highlighted cells below)
2. Or do you want to also fill in the blanks for Dates that are not visible, which would include, for example, every day in May and June 2025 in this example?

[Yuiitsu]

Thank you Owen

Just Dates that are already visible on the visual. I have gotten the answer problem fixed!