Overcoming Circular dependency Problem in DAX

[Satya]

Hi All,

Attached Power BI report contains a “Table” and “Clustered Column Chart” visuals. The report is developed by importing the tables from Excel.

Starting Inventory and Ending Inventory are the main measures required for further analysis in this and other tabs.

Dax Formulae used as below:

Starting Inventory = 
    IF (
        SUM ( Inventory[Inventory] ) = 0,
                [Ending Inventory PM],
                SUM ( Inventory[Inventory] )
    )

Ending Inventory = 
        SUM(Inventory[Inventory]) + SUM ( Actuals[Actuals] )
                - SUM ( Forecast[Forecast] )
                - SUM ( Forecast[Losses] )

In place of the “SUM(Inventory[Inventory])” in measure Ending Inventory, I should be using [Starting Inventory]. If I use, measure Starting Inventory, it errors out as “Circular Dependency”.
Inventory values will be available until last month only i.e. until September only. From October onwards, the Ending Inventory for previous month has to be loaded to Starting inventory.

Ending Inventory PM = 
    CALCULATE (
            [Ending Inventory],
            DATESINPERIOD (
                    'Date Table'[Date].[Date],
                    MAX ( 'Date Table'[Date] ) - 1, -1, MONTH
            )
    )

For October month, the Ending Inventory should be 377,927 + 110,752 – 228,589 – 6,858 = 253,232. But, it wrongly shows as -124,695. Similarly, could not calculate correct values for November and December months.

Could anyone please help me or guide me on how to fix this issue to get the correct results for Oct, Nov & Dec months?

Appreciate your immediate response on this.

Inventory Dashboard.pbix (95.7 KB)

[Melissa]

Hi Satya,

You can overcome the circular dependency by also creating PM versions for your Actuals, Inventory, Forecast and Losses, followed by these two measures:

Ending Inventory PM = 
    [Inventory PM] + [Actuals PM] -[Forecast PM] - [Losses PM]

End Inventory = 
    IF( [Inventory] =0, [Inventory PM], [Inventory] ) + [Actuals] - [Forecast] - [Losses]

I believe that should fix it.

[Satya]

Hi Melissa,

Thanks for looking into the question and proposing a solution.

Unfortunately this did not work. It yields the Ending Inventory value for October month correctly and yields wrong results for Nov and December. I hope that if we can iterate this through a while loop until the inventory value is 0, it may give correct results. Will keep trying with different options. Please let me know if any new ideas.

Thank you for your help.
Satya

[sam.mckay]

Ok a few things here.

First always use DAX formulas not matter how simple.

For example, the below should be a formula

This may seem simple, but it’s best practice and can help a lot down the track.

Then from here you should use measure branching instead of formulas like this.

Also you should have these in measure groups

Again this is simple but makes a big difference.

Also another thing I see you’ve used a dimension here Months, everythough it doen’t exist in your date table. I really don’t recommend this as there no way to then iterate through this month if we need to in a formula like SUMX.

You need a full detailed date table. It’s important here.

Also the other big issue is that the date table is only one day each month. It need to be a full date table showing every single day. You can’t use time intelligence function effectively without this.

Due to a number of issues and clean ups here I’m just going to stop here. The reality is there’s is no real point is creating a solution here until these are all updated and optimized for Power BI.

Here’s a number of links to review around all this topics.

http://portal.enterprisedna.co/courses/103686/lectures/1772404

http://portal.enterprisedna.co/courses/103686/lectures/1772347

All of these

These

These as well.

Once setup the solution here will be pretty simple I would say.

Thanks
Sam

[Satya]

Thanks Sam for correcting my mistakes. Going forward will make sure I consider these in my development. The suggested topics are very helpful to my development.

Here is the .pbix file with all the above changes implemented. Could you please guide me on the logic for this issue?

Thanks
Satya
Inventory Dashboard.pbix (200.2 KB)

[sam.mckay]

Here’s some more tips, hopefully these are helpful

Try use this formula when completing time comparisons

Ending Inventory PM = 
    CALCULATE( [Ending Inventory], 
            DATEADD( Dates[Date], -1, MONTH ))

This is nice and simple and it what this time intelligence calculation is best for.

I also can’t really reconcile the numbers now showing to the numbers you’ve mentioned above?

So I’m not really sure if there is still an issue.

I also don’t really see any problem with a circular dependency which is the title of the post?

Can you please confirm and really detail exactly what you are expecting to see.

Thanks
Sam

[Satya]

Hi Sam,

I’m Sorry if my explanation is not sufficient in my previous attempts for this question.
Please see the word document for details.

Thanks
Satya
CIrcular Dependency Issue.docx (71.5 KB) Inventory Dashboard.pbix (208.1 KB)

[sam.mckay]

Ok I understand the circular dependency now.

It’s because this formula below relies on the Ending Inventory formula

…so you can’t add this one to that particular formula.

So this has to be done another way.

…As an update I’ve spent quite a bit of time working out this mind bender.

This is where I’m at currently, I have November right but not the next line in December

These are the formula I have used.

Testing = 
VAR EndInv = [Total Inventory] + [Total Actuals] - [Total Forecast] - [Total Losses]
VAR StartInv = 
CALCULATE(
    SUMX( 
        SUMMARIZE( Dates, Dates[MonthName],
            "TotalInv", [Total Inventory],
            "TotalAct", [Total Actuals],
            "TotalFore", [Total Forecast],
            "TotalLoss", [Total Losses] ),
                [TotalInv] + [TotalAct] - [TotalFore] - [TotalLoss] ),
                    DATEADD( Dates[Date], -1, MONTH ) )

RETURN
IF( ISBLANK( [Total Inventory] ), StartInv, [Total Inventory] )

And

Testing End = 
VAR NormalEndInv = [Total Inventory] + [Total Actuals] - [Total Forecast] - [Total Losses]

RETURN
IF( ISBLANK( [Total Inventory] ),
    NormalEndInv + [Testing],
        NormalEndInv )

Have a look through the model and see if you can work out the last step.
Inventory Dashboard (3).pbix (207.1 KB)

Thanks
Sam

[Satya]

Thanks for the information and the fix relating to this.

As, I couldn’t loop through the same variables for the last month, I created separate measures and could get the desired results.
As, the inventories will not be negative in real time, I considered this condition in [Starting Inv Dec].
But, I know this is not the ideal way of doing and this approach will work only in this case.

In this case, as there is only two months left for 2019. So, I created another set of measures to get this work.
Say like in January 2020, I’ll not have 12 months Starting Inventory data and I should fetch the prior month Ending Inventory values.
So, could you please recommend me a better way of doing this?

Also, Could you please suggest me some documentation that I can refer to get better understanding to implement these sort of fixes?
Inventory Dashboard_v2.pbix (189.4 KB)

[sam.mckay]

I’m not too sure where you’re at course wise, but definitely jumping into the Mastering DAX course is a good idea.

This scenario though is quite unique. I’ve had to spend a lot of time on it myself. So I wouldn’t be too worried that is hasn’t come naturally.

I actually quite like your solution.

What you want to do if you need to grab a different year though is either.

1. Use a different context here. Instead of Month use Month and Year

2. You could still possible do it by just filtering your’re date table within a measure. (actually you’re already doing it here.

So to me you should be all good, even if you go to another year.

You’ll have to advise if I not seeing the issue clearly enough.

Thanks