Calculate prognosed total / circular dependency issue

[vincentnoot]

Hi all,

For a liquidity prognosis I want to make a measure that calculates the month's total liquid position based on previous months' expenses and prognosed liquid position.

My table has 4 rows:

- Expenses > lists all planned expenses in this month

- Cash position > The cash position at the start of the current month

- Prognosis > Equals the total of cash position + expenses for the next month

- Total > Equals the total of Expenses + Cash position + Prognosis

As the sharp reader will have realized, [Prognosis] and [Total] are in a circular dependency. However, logically I believe there has to be a way to have the total calculated based on the formula's own result in the previous month. Has anyone got some insights into this problem? 

Thanks!

[vincentnoot]

@amitchandak 

Liquidity = SUM(tb_Budget[AmountDC]) 

LiquidityCashPosition =

VAR Date_select =

IF (

MONTH ( TODAY () ) = 1 && DAY( TODAY () ) <= 16,

DATE ( YEAR ( TODAY () ) - 1, 12, 1 ),

IF ( MONTH ( TODAY () ) > 1 && DAY ( TODAY () ) <= 16,

DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) -1, 1),

IF (DAY( TODAY () ) > 16,

DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )

)))

RETURN

CALCULATE(SUM(tb_BankEntries[OpeningBalanceFC]), tb_BankEntries[Created] = Date_select)

LiquidityPrognosis = IF([LiquidityCashPosition ] > 0, 0, CALCULATE([LiquiditeitTotaal], DATEADD(dimdate[Date], -1, MONTH)))

LiquidityTotal = 

[LiquidityCashPosition ] + [Liquidity] + CALCULATE([LiquiditeitPrognosis], DATEADD(dimdate[Date], -1, MONTH))

As you can see the liquiditytotal refers to prognosis and prognosis refers to the total, but since the prognosis should use the total of the previous month, I believe it should be possible to avoid the circular dependency.

[v-yiruan-msft]

Hi @vincentnoot ,

In order to make it clearer and easier to understand your problem and find the right way to solve it, could you please provide some data from the involved tables (without sensitive data) and some practical examples to illustrate the problem and the backend logic. Thank you.

And for circular dependency, you can get more details in the following links.

Avoiding circular dependency errors in DAX

Circular Dependency between Calculated Columns in a Table in DAX 

Best Regards

[vincentnoot · Accepted solution]

Hi,

I managed to solve the problem by simply creating 12 measures (one for each month) that each take the total value of the month before.

I think my desired outcome is impossible in Power BI, as it will always lead to circularity.

[amitchandak]

@vincentnoot ,

Prognosis is using Cash position and Expenses  then they are not

and if Prognosis  using Cash position then what is the need to use it again.

Can you share actual formula