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