topic Re: Counting days excluding holiday and weekends in DAX Commands and Tips

Counting days excluding holiday and weekends

Anonymous — Fri, 25 Feb 2022 04:41:53 GMT

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 )

Re: Counting days excluding holiday and weekends

VahidDM — Fri, 25 Feb 2022 05:18:14 GMT

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/

Re: Counting days excluding holiday and weekends

Anonymous — Fri, 25 Feb 2022 05:24:34 GMT

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

Re: Counting days excluding holiday and weekends

VahidDM — Fri, 25 Feb 2022 05:39:57 GMT

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/

Re: Counting days excluding holiday and weekends

Anonymous — Fri, 25 Feb 2022 05:42:49 GMT

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

Re: Counting days excluding holiday and weekends

VahidDM — Fri, 25 Feb 2022 06:17:42 GMT

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/