Daily balance

amitchandak  thanks for the inspiration! It's my veryfirst day with powerBI so I appreciate any help. Your solution works somehow but I have problem with it.

1. It does not include the information about payment status (if invoice is overdue or not)

2. The dax formula is complex and I'm afraid if I need more advanced measure it becomes more ambiguous

3. I need to have the payment status (overdue/ in time) as a dimmenssion rather.

What do you think about that:

1. Create the new column key in the Fact table, key consist of 3 columns (ISSUE_DATE, PAYMENT_DATE, SETTLEMENT_DATE)

2. Create a bridge table, or link table, whatever, and in that table I only keep the key, dates and the new column REF_DATE that will show all dates that the key is valid

3. Join those Bridge table with Fact table on the key column  (many-to-many reletion)

4. Create calendar table and Join with Bridge table on the REF_DATE (one-to-many relation)

So my final data model will look like this:

My measures would be simplified, I can use all time intelligence functions. The only issue is the many-to-many relation (I've read it should be avoided) Do you think this may work, or I will have problems with this kind of model?