conditional lastnonblank

[jmpmolegraaf]

Hello all,

I have below measure / visual table:
I only want to show values when CostCategory = "ADD"
the filling down by lastnonblank needs to keep on working (obviously).
no matter where I try to filter, I never get a value for "ADD" only at every date.

thanks for your help!!

[Anonymous · Accepted solution]

Hi @jmpmolegraaf , hello rajendraongole1, thank you for your prompt reply!

Try the following measure:

lastnonblank = 
IF(
    MAX(UpstreamMargins[CostCategory]) = "Add",
    CALCULATE(
        LASTNONBLANK(
            UpstreamMargins[AppDate],
            SUM(UpstreamMargins[UM (USD/HL)])
        ),
        _Calendar[Date] <= MAX(_Calendar[Date])
    ),
"")

Result:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @jmpmolegraaf  - can you create a measure using LASTNONBLANK only considers rows where CostCategory = "ADD"

LastNonBlankADD =
VAR LastDate =
CALCULATE(
MAX( 'Table'[Date] ),
'Table'[CostCategory] = "ADD"
)
RETURN
CALCULATE(
MAX( 'Table'[Date] ),
'Table'[Date] = LastDate,
'Table'[CostCategory] = "ADD"
)

If the measure still doesn't work as expected, let me know how it's behaving and also share pbix file by removing sensitive data.

[jmpmolegraaf]

will test, much appreciated 🙂
just curious, you do not use lastnonblank -- why? 

[rajendraongole1]

@jmpmolegraaf -Good question! The reason I didn't use LASTNONBLANK in my first response is that the issue you're facing is more about filtering while maintaining the fill-down behavior, rather than just retrieving the last non-blank value.

Hope it works. please check 

[jmpmolegraaf]

unfortunately I can't get it to work...