topic Re: Formula - Circular dependency - Cumulative Sum? in DAX Commands and Tips

Formula - Circular dependency - Cumulative Sum?

crln-blue — Sun, 20 Dec 2020 19:54:24 GMT

Hello! (Not sure if it's okay to post it here but I really need some help)

I need to make some measures where they are dependent to each other. But PowerBI says that I have a circular dependency So I tried to find a way to make it not dependent but I can't seem to get it.

Here's the sample data:

1st month of the beginning cash is a fixed value (I'm good with this, planning to have a parameter?)

1st month of the ending cash is current beginning + cash flow.

2nd month of beginning cash is last month's ending cash.

2nd month of ending cash is current beginning +cash flow.

And so on and so forth.

Beginning and Ending Cash are dependent. I'm thinking of having another measure, True Cash, that will compute for the cumulative cash total. But I have no idea also on this part. Also, my Cash Flow is a calculated measure.

Any help is appreciated. Thank you!

Re: Formula - Circular dependency - Cumulative Sum?

amitchandak — Mon, 21 Dec 2020 01:56:34 GMT

crln-blue , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can get cumulative with help from date tbale like example

Cumm Sales = CALCULATE(SUM(Table[value]),filter(allselected(Table),Table[Date] <=max(Table[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Formula - Circular dependency - Cumulative Sum?

crln-blue — Mon, 21 Dec 2020 06:26:26 GMT

Hello amitchandak !

Thanks for the reply! However, I think it's not applicable to me since my data is from a calculated measure. However, I researched more and found my answer: Solved: Create Running Total On A Calculated Measure - Microsoft Power BI Community