Forum Discussion

KJanssens's avatar
KJanssens
Helper II
6 years ago

circular dependency when adding two independent calculated columns

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?
Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      KJanssens
      Helper II
      6 years ago

      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)