I have 2 Data Sources connected. First Data source is called 'query' and have a right joint with 2nd data source which is 'US Public Holidays'.
In Dimensions under 'query' I have Start Time and End Time. In US Public Holidays, I have Date, Day Type and Holiday Description.
Now, I would like to create a calculation field that is going to exclude weekends and Holidays from it. Because what I am trying to tell is that how many days each member on our team took off so we don't want to include Holidays and Weekends.
Thanks for you help.
Good day Kamran.
I'd suggest creating a calculated field that identifies whether it is a weekend or a holiday:
_Holiday_or_Weekend:
IF NOT ISNULL([Date]) OR DATEPART('weekday',[Start Time]) = 1 OR DATEPART('weekday',[Start Time]) = 7 THEN 'Y' ELSE 'N' END
This sees if the [Date] field exists in the Holidays table OR is a Saturday or Sunday. DATEPART with 'weekday' gives you a number representing the day of the week. 1 is Sunday, 7 is Saturday. Use this as a filter and keep 'N'.
DATEPART is a great function, you can pull out all sorts of bits from dates. I use it to calculate fiscal dates, etc.
Does this help? Best regards,
-Dan
