Forum Discussion

Rozers's avatar
Rozers
Frequent Visitor
1 year ago
Solved

Circular dependency error in Measures

Hi Everyone, Good day!

In my report, I need to calculate COGS. I have the COGS value at the beginning of January 2023, from which we determine the COGS at the end of the month.

For the following month (February 2023), the starting COGS should be the ending COGS from January 2023. This process continues, where each month's starting COGS is derived from the previous month's ending COGS, creating a sequential dependency across months.

However, while implementing this calculation, I encountered a circular dependency error since the COGS at the start of a month relies on the COGS at the end of the previous month.

Could anyone please guide me on how to resolve this issue?

Thank you!

Below are the DAX measures which I have used.

COGS Value at the End of the Month =
VAR Summary =
    SUMMARIZE(
        product,
        product[product_code],
        product[product_name],
        "RequiredQty", SUM(po[required_quantity]),
        "PlannedQty", SUM(po[planned_quantity]),
        "Wtavg", [Wt Avg PP],
        "Stock", [Starting_stock],
        "PrevMonthCOGS", [COGS Value at the Start of the Month]
    )

VAR Cal =
    SUMX(
        Summary,
        DIVIDE( ([Stock]*[PrevMonthCOGS]) +  ([Wtavg]*[RequiredQty]),
                ([Stock] + [RequiredQty])
        )
    )
RETURN
Cal


COGS Value at the Start of the Month =
VAR JanCOGS = SUM(COGS[start of the month cogs])

VAR PrevMonthCOGS =
    CALCULATE(
        [COGS Value at the End of the Month],
        FILTER(
            ALL(Dim_Date),
            Dim_Date[YearMonth] = MAX(Dim_Date[YearMonth]) - 1
        )
    )

RETURN
    IF( MAX(Dim_Date[YearMonth]) = 202301, JanCOGS, PrevMonthCOGS )

This measure showing the circular dependency error.

Note:- I have Jan-2023 COGS at the start of the month only based on that I need to calculate COGS at the start of the month and COGS at the end of the month for each month.



  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Rozers ,

    The circular dependency error occurs because the measures COGS Value at the End of the Month and COGS Value at the Start of the Month are referencing each other. This creates a loop where each measure depends on the other, making it impossible for Power BI to calculate the values.

     

    To resolve this, you need to break the circular reference. One way to do this is by using a different approach to calculate the COGS Value at the Start of the Month without directly referencing COGS Value at the End of the Month.

     

    So you can use these DAX codes below:

    PrevMonthCOGS = 
CALCULATE(
    [COGS Value at the End of the Month],
    FILTER(
        ALL(Dim_Date),
        Dim_Date[YearMonth] = MAX(Dim_Date[YearMonth]) - 1
    )
)
    COGS Value at the Start of the Month =
VAR JanCOGS = SUM(COGS[start of the month cogs])
RETURN
    IF( MAX(Dim_Date[YearMonth]) = 202301, JanCOGS, [PrevMonthCOGS] )
    COGS Value at the End of the Month =
VAR Summary =
    SUMMARIZE(
        product,
        product[product_code],
        product[product_name],
        "RequiredQty", SUM(po[required_quantity]),
        "PlannedQty", SUM(po[planned_quantity]),
        "Wtavg", [Wt Avg PP],
        "Stock", [Starting_stock],
        "PrevMonthCOGS", [PrevMonthCOGS]
    )

VAR Cal =
    SUMX(
        Summary,
        DIVIDE( ([Stock]*[PrevMonthCOGS]) +  ([Wtavg]*[RequiredQty]),
                ([Stock] + [RequiredQty])
        )
    )
RETURN
Cal

     

    If this is not what you want either, please provide relevant example data for better research.

     

     

     

    Best Regards

    Yilong Zhou

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

3 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

    Rozers Hi!

    To break this circular dependency, you need to ensure that the calculation of COGS Value at the Start of the Month does not directly reference COGS Value at the End of the Month. Instead, you can use iterative calculations using DAX variables.

     

    Instead of referencing [COGS Value at the End of the Month] directly, use a SUMX function within a calculated column or table to iterate over previous months:

    COGS Value at the Start of the Month =
    VAR JanCOGS = SUM(COGS[start of the month cogs])

    VAR PrevMonthCOGS =
    CALCULATE(
    SUMX( product, [COGS Value at the End of the Month] ),
    FILTER(
    ALL(Dim_Date),
    Dim_Date[YearMonth] = MAX(Dim_Date[YearMonth]) - 1
    )
    )

    RETURN
    IF(MAX(Dim_Date[YearMonth]) = 202301, JanCOGS, PrevMonthCOGS)

     

    Then calculate:

    COGS Value at the End of the Month =
    VAR PrevCOGS = [COGS Value at the Start of the Month]

    VAR Summary =
    ADDCOLUMNS(
    SUMMARIZE(
    product,
    product[product_code],
    product[product_name]
    ),
    "RequiredQty", SUMX( RELATEDTABLE(po), po[required_quantity] ),
    "PlannedQty", SUMX( RELATEDTABLE(po), po[planned_quantity] ),
    "Wtavg", [Wt Avg PP],
    "Stock", [Starting_stock]
    )

    VAR Cal =
    SUMX(
    Summary,
    DIVIDE( ([Stock] * PrevCOGS) + ([Wtavg] * [RequiredQty]),
    ([Stock] + [RequiredQty])
    )
    )

    RETURN
    Cal

     

    BBF

  • Rozers's avatar
    Rozers
    Frequent Visitor
    1 year ago

    Thank you for the solution, BeaBF!

    Instead of using a calculated column, I want to achieve this using measures only.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Rozers ,

    The circular dependency error occurs because the measures COGS Value at the End of the Month and COGS Value at the Start of the Month are referencing each other. This creates a loop where each measure depends on the other, making it impossible for Power BI to calculate the values.

     

    To resolve this, you need to break the circular reference. One way to do this is by using a different approach to calculate the COGS Value at the Start of the Month without directly referencing COGS Value at the End of the Month.

     

    So you can use these DAX codes below:

    PrevMonthCOGS = 
CALCULATE(
    [COGS Value at the End of the Month],
    FILTER(
        ALL(Dim_Date),
        Dim_Date[YearMonth] = MAX(Dim_Date[YearMonth]) - 1
    )
)
    COGS Value at the Start of the Month =
VAR JanCOGS = SUM(COGS[start of the month cogs])
RETURN
    IF( MAX(Dim_Date[YearMonth]) = 202301, JanCOGS, [PrevMonthCOGS] )
    COGS Value at the End of the Month =
VAR Summary =
    SUMMARIZE(
        product,
        product[product_code],
        product[product_name],
        "RequiredQty", SUM(po[required_quantity]),
        "PlannedQty", SUM(po[planned_quantity]),
        "Wtavg", [Wt Avg PP],
        "Stock", [Starting_stock],
        "PrevMonthCOGS", [PrevMonthCOGS]
    )

VAR Cal =
    SUMX(
        Summary,
        DIVIDE( ([Stock]*[PrevMonthCOGS]) +  ([Wtavg]*[RequiredQty]),
                ([Stock] + [RequiredQty])
        )
    )
RETURN
Cal

     

    If this is not what you want either, please provide relevant example data for better research.

     

     

     

    Best Regards

    Yilong Zhou

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