Historical Overdue Days - Cumulative Pattern based on FIFO Transactions

[pistachio]

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. 

[Anonymous]

Should be relatively easy, just not seeing how you are getting 150 for April first though. What would the value be on April 2nd and May 1st?

[parry2k]

@pistachio how did you get $150 as overdue on April 01 for account Jack. Numbers doesn't adds up, may be you showed partial data?

[pistachio]

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

[Anonymous]

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

[pistachio]

Thank you for your quick response but I'm having trouble understanding and implementing this and translating to my table names, particularly the circular date filters in your example.

I do have a date table. These formulas are coming up with a hugely negative value

// Does this sum overdues in the last 30 days? 
BankOD30 =
CALCULATE (
    SUM ( FactAccountTransaction[Overdue] ),
    FILTER (
        FactAccountTransaction,
        MAX ( FactAccountTransaction[DatePosted] )
            >= DATEADD ( FactAccountTransaction[DatePosted], -30, DAY )
    )
)

// Sums 30+ overdues for all time periods
BankOD RT =
CALCULATE (
    [BankOD30],
    FILTER (
        ALL ( FactAccountTransaction ),
        FactAccountTransaction[DatePosted] <= LASTDATE ( DimDate[Date] )
    )
)

[Anonymous · Accepted solution]

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])
    )
)