Holidays and weekends

Please help me with this formula. I know I need to create a table containing my holidays and link the table to my date table. How do I write the dax formula to put a 0 in all weekends and holidays?

Yes, I am embarrassed to even show you my work around. :frowning:

WorkingDays =
IF (
OR ( DateDimension[DayOfWeek] = 7, DateDimension[DayOfWeek] = 1 ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ01/01/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ01/02/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ03/30/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ05/28/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ07/04/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ09/03/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ11/22/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ11/23/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ12/25/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ12/26/2018” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ01/02/2017” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ04/14/2017” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ05/29/2017” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ07/04/2017” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ09/04/2017” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ11/23/2017” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ11/24/2017” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ12/25/2017” ),
0,
IF (
DateDimension[Date] = VALUE ( β€œ12/26/2017” ),
0,
IF ( DateDimension[Date] = VALUE ( β€œ12/31/2017” ), 0, 1 )
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

Wow, I don’t think you should do it this way.

First i really think you should have a separate table is excel or somewhere similar that you can update very easily with the holiday dates.

Then you would have this as a stand alone table. You could then use a LOOKUPVALUE function in a new column within your date table, that looks at the current date and then looks up whether it’s in the holiday table.

Once you have this column it’s a simple IF function.

You can go something like this.

IF( AND( Weekdays = Saturday, Weekday = Sunday, IsHoliday = TRUE ), 0, 1 )

This is the logic you need to step through.

Hopefully that makes sense. See how you go with this.

I really don’t like the use of the formula there with all the dates. There’s to much up keep and not enough continuity in my opinion compared to keeping a simple table that just records all the future holiday dates.

1 Like

I knew there was a better way to do this.

_IF( AND( Weekdays = Saturday, Weekday = Sunday, IsHoliday = TRUE ), 0, 1 )_

I guess β€œAND” only allows two arguments. I switched it to β€œor” by using ||

Thanks for your help Sam!!

I usually create a new column by using

Weekend - Weekday =

SWITCH (

TRUE (),

Dates[DayInWeek] = 0, "Weekend",

Dates[DayInWeek] = 1, "Weekday",

Dates[DayInWeek] = 2, "Weekday",

Dates[DayInWeek] = 3, "Weekday",

Dates[DayInWeek] = 4, "Weekday",

Dates[DayInWeek] = 5, "Weekday",

Dates[DayInWeek] = 6, "Weekend",

BLANK ()

)

Enterprise%20DNA%20Expert%20-%20Small

1 Like

I did this based on what Sam suggested. I linked to a excel sheet which has only one column, my holiday dates. I added a new column to my date table called β€œHoliday”. Then I created my final column called β€œWorkday”. Getting holidays to show as 0 was my main goal.

Workday =
IF(DateDimension[DayOfWeek] = 7 || DateDimension[DayOfWeek] = 1 || DateDimension[Holiday] >0,0,1)

Holiday =
LOOKUPVALUE(Holidays[Holiday Dates], Holidays[Holiday Dates], DateDimension[Date])

That work just fine also.

Like Sam always says there are many ways to get to the same result.

Was just my humble 2 cents.

Enterprise%20DNA%20Expert%20-%20Small

1 Like

Nice one. Looks good