Forum Discussion

Gavin_Shales's avatar
Gavin_Shales
Frequent Visitor
8 years ago
Solved

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...
Need Help
  • ForcaTaico's avatar
    ForcaTaico
    8 years ago

    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.

ForcaTaico's avatar
ForcaTaico
Resolver II
8 years ago

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's avatar
Gavin_Shales
Frequent Visitor
8 years ago

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's avatar
    ForcaTaico
    Resolver II
    8 years ago

    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.

    • Gavin_Shales's avatar
      Gavin_Shales
      Frequent Visitor
      8 years ago

       

       

       

      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's avatar
        ForcaTaico
        Resolver II
        8 years ago

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

    • KrummyPRGR's avatar
      KrummyPRGR
      Regular Visitor
      4 years ago

      How would this be simplified with a seperate date table?