Creating a 'Days Past Due' Column

[nathanisaacson]

Hi everyone,

I have a date column (due date) and I want to create two additional columns:

1. Calculates the number of work days (Mon-Fri) past the due date (assuming it is past due)
2. Calculates the number of work days (Mon-Fri) until the due date (assuming it is not yet due). 

Here is an example below (assuming the current date is 10/31/23)...

Due Date	Work Days Past Due	Work Days Until Due
10/24/23	5
10/30/23	1
10/31/23	0	0
11/1/23		1
11/7/23		5

What is the best way to create the "Work Days Past Due" and "Work Days Until Due" columns?

[v-junyant-msft · Accepted solution]

Hi @nathanisaacson ,

The following DAX might work for you:

Work Days Past Due = 
VAR Currentday = TODAY()
RETURN
    IF('Table'[Due Date] > Currentday,
    NETWORKDAYS(Currentday, 'Table'[Due Date], 1), BLANK())

Work Days Until Due = 
VAR Currentday = TODAY()
RETURN
    IF('Table'[Due Date] < Currentday,
    NETWORKDAYS('Table'[Due Date], Currentday, 1), BLANK())

I will show you when is it today by using this DAX:

Currentday = TODAY()

And the final output is shown in the following figure:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nathanisaacson]

This worked perfectly, thank you so much!

[v-junyant-msft]

Oh, sorry I got the names of the two calculated columns reversed, but the functionality is normal.

[JorgePinho]

Hi @nathanisaacson !

Check this post to see if it helps you: https://community.fabric.microsoft.com/t5/Desktop/Difference-between-dates-considering-only-working-...

[nathanisaacson]

Hi @JorgePinho ,

Thanks for the quick response!  I don't think that's exactly what I'm looking for.  I don't have a static end date - my end date is technically the current date (which would adjust automatically everyday, ideally).