Return last non blank value

[Anonymous]

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
y	01/02/2022	9	10
y	01/03/2022	11	12
y	01/04/2022	13

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?

[Whitewater100]

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.

[Whitewater100]

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!

[tamerj1]

@Anonymous 

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

[Anonymous]

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]

@Anonymous 

Check @Syk  solution. It should provide the desired result. 

[Syk · Accepted solution]

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]

thank you

very helpful