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 try to filter, I never get a value for "ADD" only at every date.


thanks for your help!!

 

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.

5 Replies

  • 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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.