topic Re: Return last non blank value in DAX Commands and Tips

Return last non blank value

Anonymous — Thu, 02 Jun 2022 13:45:52 GMT

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?

Re: Return last non blank value

Syk — Thu, 02 Jun 2022 14:16:45 GMT

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

Re: Return last non blank value

tamerj1 — Thu, 02 Jun 2022 14:21:40 GMT

Anonymous

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

Re: Return last non blank value

Anonymous — Thu, 02 Jun 2022 14:26:47 GMT

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.

Re: Return last non blank value

tamerj1 — Thu, 02 Jun 2022 14:32:44 GMT

Anonymous

Check Syk solution. It should provide the desired result.

Re: Return last non blank value

Whitewater100 — Thu, 02 Jun 2022 14:35:17 GMT

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!

Re: Return last non blank value

Whitewater100 — Thu, 02 Jun 2022 16:10:50 GMT

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.

Re: Return last non blank value

Anonymous — Thu, 28 Jul 2022 00:36:10 GMT

thank you

very helpful