Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

 

1 ACCEPTED SOLUTION
Syk
Super User
Super User

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

Syk_0-1654179384653.png

 

View solution in original post

7 REPLIES 7
Whitewater100
Solution Sage
Solution Sage

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_0-1654186148084.png

 

Whitewater100_1-1654186212153.png

 

Whitewater100
Solution Sage
Solution Sage

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.
Whitewater100_0-1654180338373.pngWhitewater100_1-1654180374756.png

 

Whitewater100_2-1654180494116.png

I hope this helps!

 

 
tamerj1
Super User
Super User

@Anonymous 

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

Anonymous
Not applicable

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.

@Anonymous 

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

Syk
Super User
Super User

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

Syk_0-1654179384653.png

 

Anonymous
Not applicable

thank you

very helpful

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors