Count of days between two dates is excluding start date

[Anonymous]

I have a formula to calculate the number of working days between a projects start and end date. However, the formula is not including the start date of the project. For example - a project starts on May 18 2020 and the formula returns 9 working days, instead of 10. Please see the formula below (I have a date table with working days flagged):

[v-yingjl]

Hi @Anonymous ,

I have created a date table like this:

If you don't want to calculate workdays excluding the start date, maybe you can just modify the formula like this(_startdate < [date]):

Net Work Days =
VAR _startDate =
    SELECTEDVALUE ( 'Table'[Start date] )
VAR _endDate =
    SELECTEDVALUE ( 'Table'[End date] )
VAR _result =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            _startDate < 'Date'[Date]
                && 'Date'[Date] <= _endDate
                && 'Date'[Day type] = "Working day"
        )
    )
RETURN
    _result

My sample file is attached that hopes to help you, please check and try it: Count of days between two dates is excluding start date.pbix  

Best Regards,
Yingjie Li

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

[Anonymous]

Any thoughts?

[Anonymous]

I need to INCLUDE the start date

[Anonymous]

Hi 

Unfortunately, that reduces the start date by 1 day

[amitchandak]

@Anonymous , In case of Datediff, you may end up Adding +1 , like for same-day date diff will give 0, we may like count that as 1 as per business need.

So add +1 in the formula.

Also, check this file how I used the calendar to get workdays

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[Anonymous]

Where can i add +1?

[mahoneypat]

if your formula is consistently off by one, you could just 

return __result + 1

Also, I'm curious why you put KEEPFILTERS around each term (vs. one of them with nexted Filter(Filter().  Hadn't seen that before.  Interesting to know that works.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Issue with adding +1 is that it adds a day to each month ( I have a month field on the columns section of the matrix)

[Anonymous]

I've even tried creating a custom column with the start date -1, but in cases where the start date is a monday, effcetively nothing happens due to the -1 date falling on a sunday (i.e., not a working day)