Calculate amount of days overdue (DYNAMIC)

[DevonVanDam]

Hi guys,

I currently have a dynamic report with all Sales data from 2017 to now. I use a year and a week slicer to show what cash is received in a week, what is newly invoiced and what the total amount of open items is.

I want to add the amount of overdue days based on the clearing date (= date payment is received) and the week + year that I have chosen in the slicers. So in week 40 of 2019 I want to see what is open (I already have that) and how many days it is overdue. Or not overdue. This should be based on the amount of days the formula gives.

Does anyone know the formula? I assume this is pretty simple so I don’t have to add my database?

Some fields I have
- clearing date
- invoice date
- due date

And I obviously have a date table!

Thanks,
Devon

[v-lid-msft · Accepted solution]

Hi @DevonVanDam ,

We could try to create measure using following formula and put into a table visual with a ID field to meet your requirement:

OverDueDay =
IF (
    AND (
        MAX ( 'Table'[DueDay] ) < MIN ( 'Calendar'[Date] ),
        OR (
            MAX ( 'Table'[ClearDay] ) < MIN ( 'Calendar'[Date] ),
            ISBLANK ( 'Table'[ClearDay] )
        )
    ),
    MIN ( 'Calendar'[Date] ) - MAX ( 'Table'[DueDay] ),
    BLANK ()
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

If you want to do this in DAX, you can use DATEDIFF() in a calculated column. 

In Power Query, you just add/subtract dates from each other. [Date1] - [Date2] for example, then format that type as a whole number.

In both cases, you just follow up with an IF() statement (or if then else in PQ if you want to do it there to minimize DAX calculations in the portal) to determine if it is past due or not. I don't think your slicer is relevent in this calculation unless I am missing something. It will simply show/hide data based on the slicer selection.