Forum Discussion
Using the latest value when a field is blank.
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.
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.
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
The formular was intented for an calculated column not a measure.
Thank you very much, Taico. It's working very nicely.
Regards
Gavin
How would this be simplified with a seperate date table?
