Forum Discussion
Solved
Invoice Payment Running Overdue Balance (Cummulative Overdue Balance)
I have a table with invoices and a table payments. I want to create a visual table and chart like the one above. Anyone who can help me is very grateful 🙏
hI Ajungx
You need to have the following setup:
- Date table (no connection)
- Invoice 1 -> * Payments
Create the following two measures:
Balance = CALCULATE ( SUM ( Invoice[Amount] ); FILTER ( ALL ( Invoice[Issue Date] ); Invoice[Issue Date] <= MAX ( 'Date'[Date] ) ) ) - CALCULATE ( SUM ( Payment[Amount] ); FILTER ( ALL ( Payment[Receive Date] ); Payment[Receive Date] <= MAX ( 'Date'[Date] ) ) ) - Overdue = CALCULATE ( [Balance]; FILTER ( ALL ( Invoice[Due Date] ); Invoice[Due Date] <= MAX ( 'Date'[Date] ) ) )
PBIX file attach
1 Reply
hI Ajungx
You need to have the following setup:
- Date table (no connection)
- Invoice 1 -> * Payments
Create the following two measures:
Balance = CALCULATE ( SUM ( Invoice[Amount] ); FILTER ( ALL ( Invoice[Issue Date] ); Invoice[Issue Date] <= MAX ( 'Date'[Date] ) ) ) - CALCULATE ( SUM ( Payment[Amount] ); FILTER ( ALL ( Payment[Receive Date] ); Payment[Receive Date] <= MAX ( 'Date'[Date] ) ) ) - Overdue = CALCULATE ( [Balance]; FILTER ( ALL ( Invoice[Due Date] ); Invoice[Due Date] <= MAX ( 'Date'[Date] ) ) )
PBIX file attach