circular dependency when adding two independent calculated columns

[KJanssens]

I have a table with PRODUCTS and a table with SALES (linked to each other with ProductID), and a DATE table (linked to SALES table)

I want to calculate the average sold items per product in the last 14 days and the average sold items in the last 28 days:

when I add the following two calculated columns to the PRODUCT table:

AvgSoldLast14Days = CALCULATE(SUM(SALES[qty]); DATESINPERIOD('Date'[Date]; NOW(); -14; DAY)) / 14

AvgSoldLast28Days = CALCULATE(SUM(SALES[qty]); DATESINPERIOD('Date'[Date]; NOW(); -28; DAY)) / 28

I get the error "a circular dependency was detected" ... 

why do I get this?

[Greg_Deckler]

Oh the wonders of DAX's refusal to do anything even remotely approaching recursion...

Well, it is tough to say. Some mocked up sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, my bet is on DATESINPERIOD doing something funky. You might try getting rid of it and replace it with a simple FILTER statement, FILTER('Date',[Date]>=NOW()-14 && [Date]<=NOW())

[KJanssens]

following on your suggestion to add sample data, I create a simple dataset. However, my sample does not have this problem, so it has something to do with my real-life data dsf ☹️

BTW: how can I add attachments (I don't see any upload button to attach my sample)

[v-lili6-msft]

HI  @KJanssens 

I have tested on my side, it works well.

Please make sure you add these two calculated column in PRODUCT table not SALES table.

and here is my sample pbix file, please try it.

and This is a good refresher on circular dependencies: https://www.sqlbi.com/articles/understanding-circular-dependencies/

For your sample pbix file, yu can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Lin