Latest Enterprise DNA Initiatives

Help with Balances from Last Month

Hi

First post here! I’m loving the tutorials which I’m powering through. In the meantime, I am really stuck on a circular dependency problem and am hoping you guys can help me out or point me in the right direction as I’m still very early on my Power BI journey.

I have a table with Opening Debtor Balances and a table with Sales Invoices & Credit Notes. I would like to take Balance (at last month end) + sales - receipts = Balance. The problem I have is that Balance is based on an older Balance thus causing a circular reference.


How can I get I fix this?
Ending Balance = if([myDate]<DATE(2018,05,01), [Opening Balance], [Ending Balance LM] + [Sales]+[Receipts by Allocation Date])

So May 2018 Ending Balance should be 15,294,457.73 and June 14,858,118.51

Many thanks

@Multiverse76,

These circular financial reference problems can be difficult to crack. To help resolve this one, I really need a PBIX file to work with. If you could please attach your current work file, that would be great. In the meantime, you may want to take a look at this thread where @ankit and I worked through a similar problem.

  • Brian

Hey Brian

Thanks ever so much for your offer of help. I took a look at the solution you worked through with @ankit and was able to apply it to my scenario. I must say it’s a very elegant solution. You’ve saved me countless hours and I learnt something I’m sure I will use many times in the future. Thank you.

Multiverse76

@Multiverse76,

Wonderful – glad to hear that was helpful.

BTW, welcome to the forum – great to have you here!

– Brian