Hello,
I have the data set with invoice details. In the data set I have an issue date, term of payment and invoice close date.
I need to prepere a chart tahat will show the balance of invoices on each day (from the min invoice issue date till today) together with information if the invoice is overdue or not.
What is the best parctise to do such analysis?
Additional question - is it possible to attach the xlsx file in this message?
Regards
@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?
