Here is what I’m trying to accomplish. We have inventory with different month ages. If it is 0-6 months old, we reserve 10% of total cost, if it’s 7-12 months old, we reserve 20% of total cost, and 50% of total cost above 12 months age.

I can get this total easily with the calculated columns below. My issue is I want to predict what our reserves will total next month, if we don’t sell anything. So I create another calculated column that adds 1 month to the current months aged calculated column and group that new column into my month groups…long story short, I get a circular dependency error. After research, it has to do with multiple calculated columns in the same table. I tried to create a measure “datediff” but I have to use Min, Max, or Sum and none of these work with my table due to different issues. How can I accomplish this?

I have a table:

partnumber, qty, cost, receive date, report run date

Measure:

Total Cost = SUMX(Table1, Table1[Qty]*Table1[Cost]

I created a calculated column:

Months Aged =

DATEDIFF('Table1’Receive Date], 'Table1’[Run Date], MONTH)

I grouped the “Months Aged” column in 0-6 Months, 7-12 Months, Over 12 Months

Created a calculated column:

E&O Reserves =

IF(Table1[Months Aged (groups)] = “0-6 Months”, [Total Cost] *.1,

IF(Table1[Months Aged (groups)] = “7-12 Months”, [Total Cost] *.2,

IF(Table1[Months Aged (groups)] = “Over 12 Months”, [Total Cost] *.5,0)))

This all works great. I want to predict what our E&O Reserves are going to be next month if we don’t sell a single item.

I created a calculated column:

Months Aged +1 =

‘Table1’[Months Aged] + 1

I grouped the “Months Aged +1” column in 0-6 Months, 7-12 Months, Over 12 Months

Created a calculated column:

E&O Reserves +1 =

IF(Table1[Months Aged (groups) +1] = “0-6 Months”, [Total Cost] *.1,

IF(Table1[Months Aged (groups) +1] = “7-12 Months”, [Total Cost] *.2,

IF(Table1[Months Aged (groups) +1] = “Over 12 Months”, [Total Cost] *.5,0)))

I get a circular error with the E&O Reserves +1 column.

I can fix all of this by just adding a column to my table through the query editor but then I would have to write the datediff function in m language because I need it to convert to two dates down to month difference. I can’t find how to use datediff in the m language.