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

[Amzadkhan]

Hello,

Hello I am new to powerbi, need help on creating a DAX measure.

I was looking towards something similar.  I am having two columns in my table as below 

1st column as ISSUE_STATUS_NAME

 
2nd column naame DUE_DATE

I want to create a measure where I want to show the Items, which are not in Issue status name "Completed" and Due date is less than today to be considered as Overdue Item.

Can you please provide me the dax formula for the same.  I have tried the above butdoes not work for me 

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