Calculating overdues over time with DAX

[Dax_Rookie]

Hi,

I'm still new to DAX and having trouble wrapping my head around Date Context.

I am trying to calculate overdue receivables at various points in time, i.e. how many receivables were overdue in Jan, Feb, Mar... e.g:

Jan > $100k
Feb > $110k
Mar > $80k

I have a transactions table ("Duplicatas") with the following columns: [State] ("approved" or "rejected"), [Purchase Date], [Due Date], [Paid Date]. There is an active connection between my [Purchase Date] and the Dates table, and inactive connections between the [Due Date] and [Paid Date] columns and the Dates table.

I want to calculate Overdues as Invoices which have [State] = "approved", [Paid Date] = BLANK () (i.e. have not been paid yet / are still oustanding), and [Due Date] < "the date of the period I am comparing to (Jan, Feb, Mar, etc.)". I am not clear on how to express that last bit in DAX.

Here my attempt:

Overdue Invoices =
CALCULATE(
    SUM( Duplicatas[Value] ),
    FILTER( ALL( Dates[Date] ), Dates[Date] = MAX( Dates[Date] )
    ),
    USERELATIONSHIP( Duplicatas[Current Due Date], Dates[Date] ),
    Duplicatas[State] = "approved",
    Duplicatas[Paid Date] = BLANK()
)

Would be grateful if anybody could point out to me what I am doing wrong!

Many thanks!

[v-chuncz-msft · Accepted solution]

@Dax_Rookie,

Check the DAX below.

Measure =
CALCULATE (
    SUM ( Duplicatas[Value] ),
    FILTER (
        ALL ( Duplicatas ),
        Duplicatas[State] = "approved"
            && Duplicatas[Due Date] < MIN ( Dates[Date] )
            && (
                Duplicatas[Paid Date] = BLANK ()
                    || Duplicatas[Paid Date] > MAX ( Dates[Date] )
            )
    )
)

[v-chuncz-msft]

@Dax_Rookie,

You just need to use the measure below.

Measure =
CALCULATE (
    SUM ( Duplicatas[Value] ),
    USERELATIONSHIP ( Duplicatas[Due Date], Dates[Date] ),
    Duplicatas[State] = "approved",
    Duplicatas[Paid Date] = BLANK ()
)

[Dax_Rookie]

Thanks a lot @v-chuncz-msft!

This formula works in parts:

It shows which invoices were due in each month that still haven't been paid. However, in October, it shows invoices that are still current (e.g. due only on Oct-30) as well.

What I am looking for is, at any given Dates[Date], the total value of invoices where the Duplicatas[Due Date] < Dates[Date].

Basically like this:

Note: I am duplicating the SUMIFS because there are two ways an invoice can be over due: Paid Date is empty (invoice hasn't been paid), or Paid Date is > Due Date (invoice was paid late, i.e. was overdue at some point).

[v-chuncz-msft · Accepted solution]

@Dax_Rookie,

Check the DAX below.

Measure =
CALCULATE (
    SUM ( Duplicatas[Value] ),
    FILTER (
        ALL ( Duplicatas ),
        Duplicatas[State] = "approved"
            && Duplicatas[Due Date] < MIN ( Dates[Date] )
            && (
                Duplicatas[Paid Date] = BLANK ()
                    || Duplicatas[Paid Date] > MAX ( Dates[Date] )
            )
    )
)

[Dax_Rookie · Accepted solution]

Thanks a lot! The following ended up working:

(R$) Overdue Invoices = 
CALCULATE (
    SUM ( Duplicatas[Value] ),
    FILTER (
        ALL ( Duplicatas ),
        Duplicatas[State] = "approved"
            && Duplicatas[Current Due Date] < MIN( MAX( Dates[Date] ), TODAY() )
            && (
                Duplicatas[Paid Date] = BLANK ()
                    || Duplicatas[Paid Date] > MAX ( Dates[Date] )
            )
    )
)