Calculate working days between two dates or a date and end of the month (if second date is blank)

[artosi]

Hi, 
I have a table called 'Voids' where there are 3 columns, one for ID, [Property Reference] and two columns for dates, [Date Void Works completed] and [Date new tenancy start]. 

In some cases, [Date new tenancy start] is blank. 

I need to calculate number of working days between the two date columns, and if the second one is blank, it needs to calculate it to the end of completed month (month just finished - report is normally run 1st of every month) 

My date table 'Date_Table' incorporates Holiday dates and has a column [Is Working Day?] to indicate if it is a working day or not, 1 = True & 0 = False. 

Essentially, the goal is to calculate number of working days (as indicated by "1" in my date table within [Is Working Day?] column) and if the [Date new tenancy start] column in my 'Voids' table is blank, it needs to use end of the previous month date. 

Here is an exanple of my 'Voids' table with desired outcome column:

			DESIRED column
Property Reference	Date Void Works completed	Date new tenancy start	Working Days Between (excluding Holiday days)		COMMENTS
3	08/08/2023	29/08/2023	14		one bank holiday in this period
4	13/07/2023		55		one bank holiday in this period plus using 30/09/2023 as end date
6	04/09/2023	25/09/2023	19		straight calculation (no holidays in this period)
5	16/09/2023		10		Using 30/09/2023 as end date

As mentioned, report is ran every start of the month (hence why it needs to take end of previous month if [Date new tenancy start] is blank. My date table already has a column to indicate previous month, using column [Previous Month] with values Yes or No. 

Any help is much appreciated. 

[Ashish_Mathur]

Share the Calendar table as well.

[OwenAuger · Accepted solution]

Hi @artosi 

Here is how I would write this as a calculated column:

Working Days Between (excluding Holiday days) = 
VAR PreviousMonthEnd =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        'Date'[Previous Month] = "Yes",
        REMOVEFILTERS ( ) -- safeguard
    )
VAR StartDate =
    Voids[Date Void Works completed]
VAR EndDate =
    COALESCE ( Voids[Date new tenancy start], PreviousMonthEnd )
VAR WorkingDayCount =
    CALCULATE (
        SUM ( 'Date'[Is Working Day?] ),
        DATESBETWEEN ( 'Date'[Date], StartDate, EndDate ),
        REMOVEFILTERS ( ) -- safeguard
    )
RETURN
    WorkingDayCount

When I tested this in a model similar to your description with UK holidays, the results were slightly different from your expected values. This could be due to a difference in holiday assumptions, an off-by-one error or something else, but it should be a simple adjustment.

I've attached my working PBIX. (I used Bravo to create the date table for convenience).

Hope that is of some help 🙂

Regards

[artosi]

@OwenAuger  thank you so much - this has worked like a charm 😁

Wish i would of thought of that. And yes, upon review, your results are correct - i somehow managed to mess up my formula in excel. 

Also, thank you for suggesting Bravo - i will definitly give it a go in a future. 

Cheers