Forum Discussion

pistachio's avatar
pistachio
Helper I
7 years ago
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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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])
    )
)
parry2k's avatar
parry2k
Super User
7 years ago

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's avatar
    pistachio
    Helper I
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        pistachio
        Helper I
        7 years ago

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