cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pistachio Frequent Visitor
Frequent Visitor

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.

DateAccountElementValueOverdue
31-Mar-19JackPmt Due 250.00
1-Apr-19Jack Pmt Received-50      (50.00)
2-Apr-19JackStatement Fee12 
1-May-19JackPmt 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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M New Contributor
New Contributor

Re: Historical Overdue Days - Cumulative Pattern based on FIFO Transactions

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 6
Super User
Super User

Re: Historical Overdue Days - Cumulative Pattern based on FIFO Transactions

@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?





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Nick_M New Contributor
New Contributor

Re: Historical Overdue Days - Cumulative Pattern based on FIFO Transactions

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?

pistachio Frequent Visitor
Frequent Visitor

Re: Historical Overdue Days - Cumulative Pattern based on FIFO Transactions

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
Nick_M New Contributor
New Contributor

Re: Historical Overdue Days - Cumulative Pattern based on FIFO Transactions

That makes more sense Smiley Happy

 

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

Running total of days overdue.png

 

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 Frequent Visitor
Frequent Visitor

Re: Historical Overdue Days - Cumulative Pattern based on FIFO Transactions

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] )
    )
)
Nick_M New Contributor
New Contributor

Re: Historical Overdue Days - Cumulative Pattern based on FIFO Transactions

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