cancel
Showing results for
Did you mean:
Highlighted
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

## Re: Calculating overdues over time with DAX

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

## Re: Calculating overdues over time with DAX

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.
Frequent Visitor

## Re: Calculating overdues over time with DAX

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

Community Support Team

## Re: Calculating overdues over time with DAX

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