cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dax_Rookie Frequent Visitor
Frequent Visitor

Calculating overdues over time with DAX

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculating overdues over time with DAX

@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] )
            )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dax_Rookie Frequent Visitor
Frequent Visitor

Re: Calculating overdues over time with DAX

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] )
            )
    )
)
4 REPLIES 4
Community Support Team
Community Support Team

Re: Calculating overdues over time with DAX

@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 ()
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dax_Rookie Frequent Visitor
Frequent Visitor

Re: Calculating overdues over time with DAX

Thanks a lot @v-chuncz-msft!

 

This formula works in parts:

Capture.JPG

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:

Capture2.JPG

 

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

Community Support Team
Community Support Team

Re: Calculating overdues over time with DAX

@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] )
            )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dax_Rookie Frequent Visitor
Frequent Visitor

Re: Calculating overdues over time with DAX

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