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])
    )
)
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] )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    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])
    )
)