Circular dependencies how to solve?

[Anonymous]

Hi All,

I have a simple scenario where one measure gets values for a previous year and another gets the values for the current year then they get summed.

For example below I have a simple table named company with a slicer on year column.

Year    sales

2020  1000

2019  500

In total we have three measures. Current year sales, Previous year sales and total for the two years.

So the first measure gets the current year sales since the slicer is set to 2020.

The previous year sales we have another measure which is the sales for the current year minus 1.

Finally, we get the two years sales by summing the two measures.

So far the third measure for the two years of sales is throwing an error about circular dependency.

Any thoughts?

Thanks!!

[Anonymous · Accepted solution]

Ok I solved the issue after I white boarded the problem. I added another measure that took the previous biennium balance measure and subtracted the current biennium total. I got no error in the new measure.

Thanks Everyone!

[Greg_Deckler]

Circular dependencies are notoriously difficult to solve. The more information the better, like formulas for example.

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

[Anonymous]

Hi Greg you are right. I just started looking at articles on this issue in general. I usually try to supply a simple version of what I am doing then I take an answer and scale it up so to speak to the actual design.

So the table actaully looks like

Slicer is BIENNIUM table below called transactions

Previous Biennium     FM01        FM02       FM 03 ...     FM12          FMTotal       Ending Balance

 1,0000                                10.00       20.00          30.0             40.00          100.00          1,100.00

So there are three measures as mentioned before

1 - Previoius Biennium

2 - FM Total

3- Total Costs

I got the circular dependenciy error when trying to put in the Previous BM total measure into the Ending Balance to get the totals

Note: I did not put in all 26 FM's (13 FM's for each year) into this response but you get the idea I hope.

So the measure for the Previous Biennium Balance:

MeasurePBNTotal = VAR

selectedYearBMMinus1 = if( HASONEVALUE('Transactions'[BIENNIUM]),

    FIRSTNONBLANK('Transactions'[BIENNIUM],'Transactions'[BIENNIUM]),

    BLANK() )-1

Return

    CALCULATE(

        'Transactions'[MeasureEndingBalance],

        'Transactions'[BIENNIUM] = selectedYearBMMinus1 )

The Measue for the FMTotal:

MeasureFMTotal = [MeasureFM01]+[MeasureFM02]+[MeasureFM03]+[MeasureFM04]

Finally for the Ending Balance Measure adds the first year FM's to the second year FM's:

MeasureEndingBalance = SUMX('Transactions',[MeasureFMTotal] + [MeasureFM13]+[MeasureFM14]+[MeasureFM15]+[MeasureFM16]+[MeasureFM17]+[MeasureFM18])

[parry2k]

@Anonymous do you have calendar dimension in your model from where you are using year or it is part of main table?

[Anonymous]

Good question.

I work in financials right now and instead of a date field it is actually a Biennium field or numbers. The other fields are Fiscal Months which I turned into numbers to replace 01, 02, 03... with 1,2,3 but I did not want to make the example too complicated.

So to make the problem hopefully more simple

Biennium   costs

20                 1000

21                 50

I hope that helps.