Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Return last non blank value

Hi everyone, I have the following table: Indicator Date Value1 Value2 x 01/01/2022 1 2 x 01/02/2022 2 3 x 01/03/2022 4 5 x 01/04/2022 6 y 01/01/2022 7 8...

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")

Icon for Solution Sage rank

Solution 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!