Circular dependency error in Measures

[Rozers]

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 · Accepted solution]

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.

[Rozers]

Thank you for the solution, BeaBF!

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

[BeaBF]

@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