Date: weekends and holidays

Question

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 · Answer

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.&nbsp;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 &gt; 0 and Weekday &lt; 6
    then 1
    else 0
in
  WeekdayFlag

// DAX
WeekdayFlag =
( WEEKDAY( DimDate[Date] ) &gt; 1
    &amp;&amp; WEEKDAY( DimDate[Date] ) &lt; 7
) * 1Your WorkdayFlag would be as follows:// Power Query
WorkdayFlag =
if [WeekdayFlag] = 1 and [HolidayFlag] = 0
then 1
else 0

// DAX
WorkdayFlag =
IF(
    DimDate[WeekdayFlag] = 1
        &amp;&amp; DimDate[HolidayFlag] = 0
    ,1
    ,0
)Then your count of working days measure is just:&nbsp;WorkingDays =
SUM( DimDate[WorkdayFlag] )Measures automatically respect filter context.

lemontree · Answer

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? &nbsp;i need a measure that &nbsp;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 &nbsp;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&nbsp;&nbsp;Days Range val = if(HASONEVALUE('Days Range Selection'[Day Range]); VALUES('Days Range Selection'[Days]);COUNTROWS(ALL('Calendar')))&nbsp;Daily Deposit = CALCULATE([Deposit Balance];FILTER('Calendar';'Calendar'[Date]&gt;TODAY()-'Days Range Selection'[Days Range val] &amp;&amp; 'Calendar'[Date]&lt;=TODAY())

greggyb · Answer

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&nbsp;WorkDays =
SUM( DimDate[WorkdayFlag] )

sabineoussi · Answer

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?

sabineoussi · Answer

Thanks&nbsp;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.

Forum Discussion

Date: weekends and holidays

7 Replies

Related content

New Date without considering Holidays or Weekends

DateDiff between two dates excluding weekends and holidays

Date Difference by excluding weekends & holidays

Adding working days to a date excluding weekends and holidays

Calculate due date based on SLA excluding weekends and holidays date conversion error