Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Return last non blank value

Hi everyone, 

 

I have the following table:

 

IndicatorDateValue1Value2
x01/01/202212
x01/02/202223
x01/03/202245
x01/04/20226 
y01/01/202278
y01/02/2022910
y01/03/20221112
y01/04/202213 

 

I would like to extract from this table the last available non blank value for Value2 for value 'x' as Indicator. In this situation that means I want to extract '5' as this is the last available value in time for indicator x and in column Value2. I tried with LASTNONBLANKVALUE but cannot figure it out. Can anyone help me?

 

  • Syk's avatar
    Syk
    4 years ago

    Try this..

    Measure = 
var maxdate = CALCULATE(MAX('Table'[Date]),'Table'[Indicator] = "x", NOT ISBLANK('Table'[Value2]))
return CALCULATE(MAX('Table'[Value2]),'Table'[Date]=maxdate,'Table'[Indicator] = "x")

     

7 Replies

  • Syk's avatar
    Syk
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    Try this..

    Measure = 
var maxdate = CALCULATE(MAX('Table'[Date]),'Table'[Indicator] = "x", NOT ISBLANK('Table'[Value2]))
return CALCULATE(MAX('Table'[Value2]),'Table'[Date]=maxdate,'Table'[Indicator] = "x")

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thank you

      very helpful

       

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi:

    If you want a more simple measure using LASTNONBLANK, you can use:

    Lst Price Data =
    CALCULATE(LASTNONBLANKVALUE(Dates[Date], MAX(Data[Value2])), ALLEXCEPT(Data, Data[Indicator]))
     
    Still using file sent earlier.

     

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 

    Value1 and Value2 are measures or columns? How are going to disply thhe result?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Value1 and Value2 are calculated columns of the table. I want to have conditional markup based on the requested value, so I need it returned as a singular value.

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous 

        Check Syk  solution. It should provide the desired result. 

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi:

    I did this by first putting a calc column in the table (I called table "Data")

    Then I followed with a measure. * I have a Date Table Attached.

    Calc Col:

    Last Price by Indicator =

    VAR PreDate_ =
    CALCULATE (
    MAX ( Data[Date] ),
    FILTER (
    ALLEXCEPT ( Data,Data[Indicator] ),
    Data[Date] < EARLIER ( Data[Date] )
    )
    )
    RETURN
    IF (
    Data[Value2] > 0,
    Data[Value2],
    CALCULATE (
    MIN ( Data[Value2] ),
    FILTER ( ALLEXCEPT ( Data, Data[Indicator] ), Data[Date] = PreDate_ )
    )
    )
     
    Measure:
    Last Value2 = LASTNONBLANKVALUE(Dates[Date], MAX(Data[Last Price by Indicator]))
     
    Images: * you can have a filter for indicator if you only want to see X.

     

    I hope this helps!