Using the latest value when a field is blank.

[Gavin_Shales]

Good day All,

I am look for some help.

I have a table which currently has some missing values and I am trying to populate these missing values with the most recent value at that point in time. For example;

Date                       Value

04/10/2017            1641

05/10/2017            1698

06/10/2017            1742

07/10/2017

08/10/2017

09/10/2017             1660

10/10/2017              1509

11/10/2017              1517

12/10/2017              1645

13/10/2017              1655

14/10/2017

15/10/2017

The 7th and 8th was a Saturday and Sunday, so there was no input on that day. What I need populated is 1742 for both the 7th and 8th. The same for the 14th & 15th but populated with 1655.

Please can somebody help me with this? Thank you very much.

[ForcaTaico]

Hello,

In the query editor you can sort by Date and then right click on the Value column then choose "Fill" --> "Down".

Regards Taico

[Gavin_Shales]

Hi Taico,

Thanks for the reply.

I don't think I can do this because I am using 2 data sources in my table.

DataSource (DS) A contains a list of running dates and DS B contains just data for the days the data has been inputted. Therefore, I can't use query editor as the Data from DS isn't in the same table as the list of the Running dates.

That is why I was hoping for a DAX solution. Admittedly my PowerBI knowledge and experience is pretty low so don't feel like I am telling you your solution won't work.

Can I create a new column in DS A to bring in the values from DS B, via the Query Editor?

Thanks

[ForcaTaico · Accepted solution]

Hi

ValueLast = LOOKUPVALUE(Table1[Value],Table1[Date],CALCULATE(LASTNONBLANK(Table1[Date], 1), FILTER(Table1,Table1[Value]>0 && EARLIER(Table1[Date])>=Table1[Date])))

If you have a Date-table the above formular could be simplified... but it should work.

[KrummyPRGR]

How would this be simplified with a seperate date table?

[Gavin_Shales]

Hi Taico,

Hopefully you can see my Pic which i have added.

Basically, it's not liking the "Earlier" part of the code. It says, "Earlier/Earliest referes to an earlier row or context which doesn't exist"

Should I be using this code in a "New Measure"?

Thanks

[ForcaTaico]

The formular was intented for an calculated column not a measure.

[Gavin_Shales]

Thank you very much, Taico. It's working very nicely.

Regards

Gavin