Solved: Re: Working days

Anonymous · ‎01-13-2017

This is my current formula:

WorkingDays = IF (OR (DateDimension[DayOfWeek] = 7, DateDimension[DayOfWeek] = 1), 0, if(DateDimension[Date] = value("01/01/2016"),0,if(DateDimension[Date] = value("03/25/2016"),0,if(DateDimension[Date] = value("05/30/2016"),0,if(DateDimension[Date] = value("07/04/2016"),0,if(DateDimension[Date] = value("09/05/2016"),0,if(DateDimension[Date] = value("11/24/2016"),0,if(DateDimension[Date] = value("11/25/2016"),0,if(DateDimension[Date] = value("12/23/2016"),0,if(DateDimension[Date] = value("12/26/2016"),0,if(DateDimension[Date] = value("12/30/2016"),0,1)))))))))))

I of course hate this for obvious reasons. I have a holiday and a datedimension table.

The holiday table has the holidays in it so I think I need to use the RelatedTable function somehow. Any suggestions?

Thanks for your help.

Greg_Deckler · ‎01-13-2017

Perhaps you could consider doing a Merge query in the query editor with the appropriate join (left anti?) to essentially filter out your holidays from your DateDimension table?

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Greg_Deckler · ‎01-13-2017

Perhaps you could consider doing a Merge query in the query editor with the appropriate join (left anti?) to essentially filter out your holidays from your DateDimension table?

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...