Counting days excluding holiday and weekends

[Anonymous]

There is a couple of messages regarding this but I have found the easiest might be this code by creating a calendar table and number 1-7 the days. But I also included a number 8 for publich holiday. This code also counts if start date and end date is on the same date that it is 1 where a lot does not count it. The problem that I'm facing is in the or statement where it looks like you can only have two arguments where I need their either 1, 7 and 😎 as listed below

Counting =

CALCULATE (

COUNTROWS ( 'Calendar' ),

FILTER (

'Calendar',

AND (

'Calendar'[Date] >= 'Leave'[Leave Start Date],

'Calendar'[Date] <= Leave[Leave End Date]

)

),

OR ( 'Calendar'[DayNumber] = 1, 'Calendar'[DayNumber] = 7, 'Calendar'[DayNumber] = 8 )

[Anonymous]

As a calculated colomn

No	Leave Start	Leave End
100	23/03/2021	23/03/2021
200	25/03/2021	30/03/2021
100	26/03/2021	28/03/2021
300	1/04/2021	3/04/2021
300	10/04/2021	11/04/2021

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this then as a cal. column:

Counting =
CALCULATE (
    COUNTROWS ( 'Calendar' ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] >= 'Leave'[Leave Start Date]
            && 'Calendar'[Date] <='Leave'[Leave End Date]
            && 'Calendar'[DayNumber] IN { 2, 3, 4, 5, 6 }
    )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Thanks for the quick response. I tried it but it just returns blanks although should be days to count

[VahidDM]

@Anonymous 

Can you pleae share a sample of your tables in a text format?

Do want a measure? or calculated column?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[VahidDM]

Hi @Anonymous 

Try this:

Counting =
CALCULATE (
    COUNTROWS ( 'Calendar' ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] >= MAX ( 'Leave'[Leave Start Date] )
            && 'Calendar'[Date] <= MAX ( 'Leave'[Leave End Date] )
            && 'Calendar'[DayNumber] IN { 2, 3, 4, 5, 6 }
    )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/