Date: weekends and holidays

[SabineOussi]

Hello,

I have a starting date and an ending date and I need to count only the working days in between. In other words, the date difference (DATEIFF) between the ending and the starting without taking into consideration the weekends and the holidays.
I tried using WEEKDAY but I couldn't manage to get to the days within the given interval and decrease the difference count at every special date encounter.

How can I achieve this?

Thank you,
Sabine O.

[greggyb]

Create a field WorkdayFlag that is 1 when the date in question is a working day (not weekend and not holiday, might be useful to maintain separate WeekendFlag and HolidayFlag fields). Then you can just use

WorkDays =
SUM( DimDate[WorkdayFlag] )

[SabineOussi]

The thing is I need to check every day within the (start date - end date) interval and remove the weekends and holidays, if any.
How can I do that?

[greggyb]

You don't need to check those conditions if you have a field as I described. The SUM() measure will automatically respect any filters you apply, so you'll get the count of working days. You can apply a filter with a slicer, a visualization cross-filter, or one of a visual-, page-, or report-level filter.

Your holidays must come from some source table because there are no built in functions that know when your organization has holidays. A weekday check is simple, just check for

// Power Query
WeekdayFlag =
let
  Weekday = Date.DayOfWeek( [Date] )
  ,WeekdayFlag =
    if Weekday > 0 and Weekday < 6
    then 1
    else 0
in
  WeekdayFlag


// DAX
WeekdayFlag =
( WEEKDAY( DimDate[Date] ) > 1
    && WEEKDAY( DimDate[Date] ) < 7
) * 1

Your WorkdayFlag would be as follows:

// Power Query
WorkdayFlag =
if [WeekdayFlag] = 1 and [HolidayFlag] = 0
then 1
else 0



// DAX
WorkdayFlag =
IF(
    DimDate[WeekdayFlag] = 1
        && DimDate[HolidayFlag] = 0
    ,1
    ,0
)

Then your count of working days measure is just:

WorkingDays =
SUM( DimDate[WorkdayFlag] )

Measures automatically respect filter context.

[SabineOussi]

Thanks @greggyb,

Your input was much valuable to make me understand what you previously mentioned.
However, I can't see where I can put my closing date so that it takes the days in between.

As I said before, I only have a start date and an end date and I need to check the days in between whether they were working days or not.

Thanks again,

Sabine O.

[SabineOussi]

I just discovered NETWORKDAYS and NETWORKDAYS.INTL in Excel. That is exactly what I want, if possible.

[greggyb]

You can set up a visual-, page-, or report-level filter and choose a starting date and ending date in there based on your date field in your date dimension.

[lemontree]

How the solutions is changing if we need calculate the number of days between today and x amount of days back. The page will not have an date filter / slicer on it, but instead I want to create a pre-selected slicer with number of days to select from, for example 2, 7 days or 14, 30 days, from current date?  i need a measure that  will take into consideration any weekends or holidays and when a user clicks on the day range on the slicer the tables / charts in the page to update for the last 2, 7, 14, or 30 working days  instead of actual calendar days. if today is a Monday and I click 2 days on the slicer day range selection and I have applied a report level filter for only the trading days, then it does not bring back anything. as it the way the day range harvested measure works is to go back 2 days from today() day and it cannot take into consideration the fact that it is a weekend/holiday

 Days Range val = if(HASONEVALUE('Days Range Selection'[Day Range]); VALUES('Days Range Selection'[Days]);COUNTROWS(ALL('Calendar')))

Daily Deposit = CALCULATE([Deposit Balance];
FILTER('Calendar';
'Calendar'[Date]>TODAY()-'Days Range Selection'[Days Range val] && 'Calendar'[Date]<=TODAY())