Forum Discussion

OS1979's avatar
OS1979
Frequent Visitor
3 years ago
Solved

Do I need a circular dependency?

Hello everyone,

 

I need a little help for a capacity sheet in Power BI:

For example I have for one month the following columns:

Requirement previous month (Measure), Requirement, cumulative requirement (Requirement + Rerequirement previous month) (Measure), free capacity and result (cumulative requirement - free capacity) (Measure) . 

So if there is not enough free capacity in a month we put the result in the next month measure like this:

 

Requirement previous month = IF(CALCULATE([cumulative requirement],PREVIOUSMONTH(TAP02N[Datum].[Date])) - CALCULATE(SUM(Endergebnis[free capacity),PREVIOUSMONTH(TAP02N[Datum].[Date])) >0, CALCULATE([kum. Bedarfe],PREVIOUSMONTH(TAP02N[Datum].[Date])) - CALCULATE(SUM(Endergebnis[cumulative requirement]),PREVIOUSMONTH(TAP02N[Datum].[Date]))  , 0 )
 
And now your are thinking right, i get an error for circular dependency if i want to calculate the result. 
But is there a chance for fixing this????
 
Greetings and thanks for any advice
Oliver

 

 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    OS1979 , If you want to  haveexcel like recursive calculation then it is not possible

     

    A2= A1 + C1

    A3= A2 + C2

     

    You have to think cumulative like inventory calc

     

    Inventory / OnHand
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

     

    Power BI Inventory On Hand
    Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    OS1979 , If you want to  haveexcel like recursive calculation then it is not possible

     

    A2= A1 + C1

    A3= A2 + C2

     

    You have to think cumulative like inventory calc

     

    Inventory / OnHand
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

     

    Power BI Inventory On Hand
    Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

  • OS1979's avatar
    OS1979
    Frequent Visitor
    3 years ago

    Good morning and thank you very much for this idea. I will try it 🙂

  • OS1979's avatar
    OS1979
    Frequent Visitor
    3 years ago

    ... and it helped me a lot : -) .  I have to rethink my matrix but at the end I thing I have a good solution to present.
    Thank you very much