Forum Discussion

jmpmolegraaf's avatar
jmpmolegraaf
Frequent Visitor
1 year ago
Solved

conditional lastnonblank

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 tr...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
rajendraongole1
Super User
1 year ago

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's avatar
jmpmolegraaf
Frequent Visitor
1 year ago

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

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      jmpmolegraaf
      Frequent Visitor
      1 year ago

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