Trying to get the number of working days between a date range when the date doesn't change.

[Anonymous]

As you can see below I've created a Calculated WorkDay column.
My test data show the from Status and To Status date range for the four records.
Four changes have been made for the same Item Number. As you can see the first entry didn't record a day but the next three entries did. Is this right I would think that if the date never changes it shouldn't count as a day. I also tried removing the time but got the same results. Any suggestion would be greatly appreciated.

Thanks.

Test data

[v-zhangti]

Hi, @Anonymous 

Please check the following tests.

days = DATEDIFF([From],[To],DAY)

Workday =
CALCULATE (
    COUNT ( 'Date'[Date] ),
    DATESBETWEEN ( 'Date'[Date], 'Table'[From], 'Table'[To] )
)

Your formula will be one day longer than the date count. You can try the following methods.

Workdays = 
CALCULATE (
    COUNT ( 'Date'[Date] )-1,
    DATESBETWEEN ( 'Date'[Date], 'Table'[From], 'Table'[To] ),
    FILTER ( 'Date', [IsWorkingday] = 1 )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[amitchandak · Accepted solution]

@Anonymous , You need a column like

countrows(filter(addcolumns(calendar('Table'[From Status Date],'Table'[To Status Date],)), "WorkDay", weekday([Date],2)),[WorkDay]<6)

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

[Anonymous]

@amitchandak  Thanks for the update... but if the from status date and To status date never changes how can that be counted as 1 day for each record. I would think that the work day would be 0 days or only one day. I put in your calculation and got for working days.