I have to calculate the days of delay for each invoice based on the date of the current title in the PivotTable.
The calendar table contains all dates from 2017-01-01 to 2019-12-31
The invoice table contains the following columns
InvoiceNumber InvoiceDate InvoiceDueDate InvoiceAmount
The billing table has an active relationship with the calendar table
Calendar[Date] -> Invoice[InvoiceDate]
PivotTable should look like this
enter the description of the image here
Bill 1234 was due on 2018-10-01, so she was late on 2018-10-07 and 2018-10-14. He was paid on 2018-10-18, so he was not late on 2018-10-31.
Bill 5566 was placed on 2018-10-13 and paid on 2018-10-18. It was late on 2018-10-14.
Bill 9877 was due on 2018-10-13 and had not yet been paid on 2018-10-31. He was late on 2018-10-14 and 2018-10-31