Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dax - Optimise LASTNONBLANKVALUE

Hello,

I currently have a model that relies heavily on the LASTNONBLANKVALUE function to act as a (forward) filldown of the plants' costs that are missing for certain dates. I have a calendar table which encompass every day between the first and last day of my fact table. All my visuals only show dates that are the first of the month.

The LastNonblankValue is so ressource intensive that in some instance visual won't display anything as the memory requirement is over the 1024MB limit.

Would you have input on how to restrict the scope of the function actions?

Here's one of the measure using lastnonblank and an excerpt of my fact table :

Price xUM =
CALCULATE(
    LASTNONBLANKVALUE(
        _Calendar[Date],
        CALCULATE(SUM(PurchasePrices[CostValue (USD/HL)]), PurchasePrices[CostType] <> "Upstream")
    ),
    _Calendar[Date] <= MAX(_Calendar[Date])
)
RIPCodeCostTypePlantCodeApplDateCostValue (USD/HL)CostCategory
XYB CostP1211/18/2024156.36B
XYB CostP1210/1/2023168.17B
XYD CostP1211/18/202431.91D
XYD CostP1210/1/202331.89D
XYMarginP1211/18/202410.57NM
XYAdmin FeeP1211/18/20247.12NM
XY FeeP1211/18/202416.02NM
XYMarginP1210/1/202310.94NM
XYAdmin FeeP1210/1/20237.12NM
XY FeeP1210/1/202311.57NM
XYUpstream P122/1/2025-22.00D
XYUpstream P121/16/2025-22.00D
XYUpstream P121/1/2025-22.00D
XYUpstream P1212/1/2024-19.70D
XYUpstream P1211/1/2024-19.70D
XYUpstream P1210/1/2024-19.70D
XYUpstream P129/16/2024-19.70D
XYUpstream P129/1/2024-19.70D
XYUpstream P128/1/2024-19.70D
XYUpstream P127/16/2024-19.70D
XYUpstream P127/1/2024-19.70D
XYUpstream P126/1/2024-19.70D
XYUpstream P125/16/2024-19.70D
XYUpstream P125/1/2024-19.70D
XYUpstream P124/16/2024-19.70D
XYUpstream P124/1/2024-19.70D
XYUpstream P123/16/2024-19.70D
XYUpstream P123/1/2024-19.70D
XYUpstream P122/1/2024-19.70D
XYUpstream P121/16/2024-19.70D
XYUpstream P121/1/2024-19.70D
XYUpstream P1212/16/2023-19.70D
XYUpstream P1212/1/2023-19.70D
XYUpstream P1211/16/2023-19.70D
XYUpstream P1211/1/2023-19.70D
XYUpstream P1210/16/2023-19.70D
XYUpstream P1210/1/2023-19.70D
XYUpstream P129/16/2023-19.70D
XYUpstream P129/1/2023-19.70D
XYUpstream P128/16/2023-19.70D
XYUpstream P128/1/2023-19.70D
XYUpstream P127/16/2023-19.70D
XYUpstream P127/1/2023-19.70D
XYUpstream P126/16/2023-19.70D
XYUpstream P126/1/2023-19.70D
XYUpstream P125/16/2023-19.70D
XYUpstream P125/1/2023-19.70D

In this case the measure works and  is able to display values for all CostType up until first of Feb 25 for the the plant P12 but it requires too much memory.

Thanks a lot for your time and let me know if more info are needed!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    Due to I don't know what column you add into line chart Legend, here I use CostCategory in sample.

    I suggest you to try code as below.

    Price xUM 1 =
VAR MaxDate =
    CALCULATE (
        MAX ( PurchasePrices[ApplDate] ),
        FILTER (
            ALLEXCEPT ( PurchasePrices, PurchasePrices[CostCategory] ),
            PurchasePrices[CostType] <> "Upstream"
                && PurchasePrices[ApplDate] <= MAX ( _Calendar[Date] )
        )
    )
RETURN
    CALCULATE (
        SUM ( PurchasePrices[CostValue (USD/HL)] ),
        PurchasePrices[CostType] <> "Upstream",
        _Calendar[Date] = MaxDate
    )

    Result is as below.

    The new one show a better performance.

     

    Best Regards,
    Rico Zhou

     

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

     

6 Replies

  • Abhilash_P's avatar
    Abhilash_P
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous ,

    Can you try with below DAX function


     

    Scope Wt Avg =
    VAR MaxApplDate =
    CALCULATE(
    MAX(PurchasePrices[ApplDate]),
    FILTER(
    PurchasePrices,
    PurchasePrices[ApplDate] <= MAX(_Calendar[Date])
    )
    )

    VAR WeightedValue =
    CALCULATE(
    SUM(Products[Weight]),
    PurchasePrices[ApplDate] = MaxApplDate
    )

    RETURN
    IF(NOT ISBLANK(WeightedValue), WeightedValue)

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, thanks a lot Abhilash however I'm so sorry I posted the wrong DAX measure .... I edited the post but the measure I needed help with was :

      Price xUM =
      CALCULATE(
          LASTNONBLANKVALUE(
              _Calendar[Date],
              CALCULATE(SUM(PurchasePrices[CostValue (USD/HL)]), PurchasePrices[CostType] <> "Upstream")
          ),
          _Calendar[Date] <= MAX(_Calendar[Date])
      )

      However I think your approach of creating a custom measure to replace the Lastnonblank might be the solution I'm looking for .

      • Abhilash_P's avatar
        Abhilash_P
        Icon for Super User rankSuper User
        1 year ago

        Hi Anonymous ,

        Can you check with below dax

        Price xUM =
        VAR MaxDate =
        MAX(_Calendar[Date])

        RETURN
        CALCULATE(
        SUM(PurchasePrices[CostValue (USD/HL)]),
        PurchasePrices[CostType] <> "Upstream",
        _Calendar[Date] <= MaxDate
        )

        Let me know if these works