Hello,
I have a Customers table that’s linked to a Transactions Table and am using Direct Query mode.
I’ve created a calculated column on the customers table that finds the date of the customer’s earliest sales order:
FirstSalesOrderDate =
CALCULATE(
MIN('Transactions'[Date]),
'Transaction'[Type] = "Sales Order"
&& 'Transaction'[State] <> "Closed"
&& 'Transaction'[State] <> "Cancelled"
)
This works as desired.
My problems occur when I try and add a second calculated column to the Customers table that returns a date with just the year and month from the FirstSalesOrderDate. EG: The date 2023-05-24 becomes 2023-05-01:
FirstSOMonthYear =
DATE(YEAR([FirstSalesOrderDate]), MONTH([FirstSalesOrderDate]), 1)
This creates a circular dependency for some reason, yet FirstSalesOrderDate in no way references FirstSOMonthYear.
I’ve also tried changing the calculated column FirstSOMonthYear to this:
firstSOMonthYear =
VAR theDate = CALCULATE(
MIN('Transaction Header'[Date]),
'Transaction Header'[Type] = "Sales Order"
&& 'Transaction Header'[State] <> "Closed"
&& 'Transaction Header'[State] <> "Cancelled"
)
RETURN DATE(YEAR(theDate), MONTH(theDate), 1)
This too results in a circular dependency, yet it in no way references the first calculated column.
What I want to know is why I’m seeing this behaviour, and what I can do to avoid it.
Can anyone help please?
