Forum Discussion
Solved
Historical Overdue Days - Cumulative Pattern based on FIFO Transactions
I have a database of account transactions, each transaction has an overdue element (or blank) e.g.
| Date | Account | Element | Value | Overdue |
| 31-Mar-19 | Jack | Pmt Due | 250.00 | |
| 1-Apr-19 | Jack | Pmt Received | -50 | (50.00) |
| 2-Apr-19 | Jack | Statement Fee | 12 | |
| 1-May-19 | Jack | Pmt Received | -50 | (50.00) |
The total overdues can be calculated using the cumulative pattern:
Overdue balance :=
CALCULATE (
SUM ( Transactions[Overdue] ),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= MAX( 'Date'[Date] )
)
)I want to find the balance which is more than 30 days overdue in the current context. E.g. On 1 Apr this account had $150 which was more than 30 days overdue.
try these:
TOTAL OVERDUE = CALCULATE( SUM( FactAccountTransaction[Overdue]), FILTER( DimDate, MAX(DimDate[Date]) >= DATEADD( DimDate[Date], -30, DAY )) ) Total OverDue RT = IF( COUNTROWS(FactAccountTransaction) >=1, CALCULATE( [TOTAL OVERDUE], FILTER( ALL( DimDate), MAX( DimDate[Date]) >= DimDate[Date]) ) )
6 Replies
Sorry I got my months mixed up. The account was $150 overdue on 1 May ($250 expected less $100 received since).
- 31 March - Acct is $250 overdue, 0 days overdue
- 1 April - Acct is $200 overdue, entire overdue amt is 1 day overdue
- 20 April - Acct is $200 overdue. If there was a $50 payment expected this day the total overdue would increase by $50 (to $250) but only $200 would be 20 days overdue.
- 1 May - Acct is $150 overdue, 30 days overdue
That makes more sense :)
I did this by these two measures:
TOTAL OVERDUE = CALCULATE( SUM( Table1[Overdue]), FILTER( Table1, MAX( Table1[Date]) >= DATEADD( Table1[Date], -30, DAY )) ) Total OverDue RT = CALCULATE( [TOTAL OVERDUE], FILTER( ALL( Table1), MAX( Table1[Date]) >= Table1[Date]))You will want to have a Calendar table that is related to your fact table though and use the dates from the Calendar table for your rows and in the filter arguments above
