Forum Discussion

Tasos's avatar
Tasos
Helper II
7 years ago

Circular dependency in columns with different dates

Hello,

 

I am trying to recreate a relatively easy calculation in Excel in PowerBI. However, it returns a circular dependency.

 

In Excel, I don't have this issue as, although I use data from the same column, I am using a different row. I have tried to use the calculate function as it is proposed in previous posts regarding the circular dependency, but with no success.

 

I have added both an Excel sample and the PowerBI template with my so far calculations for the same sample.

 

Thank you for your support!!

 

Tasos

 

General Comment
Need Help
Show and Tell

4 Replies

  • 101Mathew's avatar
    101Mathew
    Advocate V
    7 years ago

    Why not do this in Power Query/Editor.

     

     Just create a custom column, and you can do all sorts of calculations

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Tasos,

     

    We can create measures to work on it, then we will get no error.

     

    awd0m = CALCULATE(SUM(Table1[Demand]))
    awd1m = CALCULATE(SUM(Table1[Demand]),DATEADD('Table1'[Date],-1,DAY))
    awd2m = CALCULATE(SUM(Table1[Demand]),DATEADD('Table1'[Date],-2,DAY))
    CWD2M = IF(ISBLANK([awd1m]),BLANK(),IF(MAX(Table1[Capacity])>[awd2m],[awd2m],MAX(Table1[Capacity])))

     

    Regards,

    Frank

     

    • 101Mathew's avatar
      101Mathew
      Advocate V
      7 years ago

      TasosI agree with you solution, if the original data is still required otherwise cut down the on the data loaded into the report when possible.

       

      Regards,

      Mathew

    • Tasos's avatar
      Tasos
      Helper II
      7 years ago

      Hi v-frfei-msft,

       

      Unfortunately, your approach was not what I am looking for. I am currently trying 101Mathew's solution working in Power Editor, although I am not sure how this can help.

       

      Thank both of you for your time,

      Tasos