Forum Discussion
Using the latest value when a field is blank.
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.
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.
7 Replies
Hello,
In the query editor you can sort by Date and then right click on the Value column then choose "Fill" --> "Down".
Regards Taico
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
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.
