Forum Discussion

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
6 years ago

Excluding weekends from the difference of dates.

Hi there,   I have a date table, one of the columns contains the name of week name. I want to calculate the number of days between the start date and end date and exclude the weekend. Below is the ...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI Dunner2020 

It is missing Dates[IsHoliday] column.

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
6 years ago

Anonymous ,

 

That's strange. Because I can see IsHoliday column. IsHoliday contains either False or True Values.In the sample, all values of IsHoliday is false except one date i.e. 15/1/2019

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Dunner2020 

    My bad, I didn't scroll to the right.

    I got 22 as a result. My [Is Holiday] column is Text and used the below formula. You can change the [start date], [end date] and make sure they are in the correct format and compared with Dates[Date] column.

     

    Difference in days = CALCULATE( COUNTROWS(Dates), FILTER(ALL(Dates)
                            , Dates[Date] >= DATE(2019,01,01) &&
                              Dates[Date] <= date(2019,01,31) &&  Dates[IsHoliday] = "FALSE" 
                              &&  (Dates[Day Of Week Name] <> "Saturday" &&
Dates[Day Of Week Name] <> "Sunday")  )  )

     

     

     

    • Dunner2020's avatar
      Dunner2020
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Anonymous , When I ran your code, visualization did not work. I think the reason might be ISHoliday is a calculated column Which has either value 'False' or 'True'. In my code, I was checking holiday condition like date[ISHoliday]=False(), which you changed it to date[ISHoliday]="FALSE".