finding dates for weekday in the future
in our business we have a feature that lets users book a timeslot and weekday to recieve their service , for example ,user X booked his service to be every Friday 10:00 AM , user Y has booked his service to be every Tuesday 1:00 PM So user X will recieve his service every Friday 10:00AM and user Y will recieve his serive every Tuesday 1:00 PM the request is : create a matrix for every future date , how many users have booked a specific timeslot , ex: tuesady 06-29-2021 Wednesday 06-30-2021 Thursday 07-01-2021 Friday 07-02-2021 Saturday 07-03-2021 Sunday 07-04-2021 Monday 07-05-2021 tuesady 07-06-2021 Wednesday 07-06-2021 Thursday 07-07-2021 Friday 07-08-2021 10:00 AM 1 User 1 User 11:00 AM 12:00 PM 1:00 PM 1 User 1 User so as you can see , dates are in the futute , and do not exist in our database , the data i have is : -User ID -weekday -time -service date ( data will be shown once service date has passed , if the date hasnt come yet , data will be NULL) ex for the table : User ID Week day Time Serive date 124 Tusday 6:00 PM 6/27/2021 567 Friday 11:00 AM NULL 566 Sunday 4:00 PM NULL 433 Friday 3:00 PM NULL so how can i find for each weekday and time the date for it in the future evey month ?Simple way to calculate working days between two dates.
Hi all, I've search all round this forum to find a simple solution to figure out number of working days between two dates. i.e. excluding weekend. I actually had to use two existing posts and some existing skills to make it a bit easier to understand. All dates in my example are hypothetical....Here it goes: Start with creating new table that will act as a calendar Next use this in formula bar of newly created table (you're giving it a name and it's first column) MyCalendar = CALENDAR(MIN('Activivty History'[Latest Baseline Dispatch Date].[Date]),MAX('Sub Project w Activities'[ACTUAL_START].[Date])) Explanation: CALENDAR funtion returns a table that contains a set of dates, it names your column simply as Date, whereas the usage of MIN and MAX indicates your date ranges of your date set. So I'm using MIN date found in Latest Baseline Dispatch Date column and MAX date found in Actual Start column. You should see something like this so far: Next we need to figure out week days within date range we just created. Create new column and copy and paste this: Week Day = WEEKDAY(MyCalendar[Date].[Date],2) As per documentation this function "Returns a number from 1 to 7 identifying the day of the week of a date". Syntax for this is: WEEKDAY(<date>, <return_type>). There are three different return types, read here more about it. I have used 2 in above code as it indicated week beggining on Monday (1) and ending on Sunday (7). You should now have something like this: You can see your 6 & 7 which is your Saturday and Sunday (feel free to check the calendar like I did to see if this was right) Next what you want to do is create a simple IF statement that determines if it's a working day or not, so use this: Working Day = IF(DimDate[Week Day] in {1,2,3,4,5},"True","False") This IF statement simply goes and checks what the week day is and if it falls within 1-5 range returns True otherwise it returns False. Now return to your table where you want to figure out your working days between two dates and do this: WorkingDays Early or Late = COUNTROWS( FILTER(MyCalendar, AND( AND( MyCalendar[Date].[Date] >='Activivty History'[Latest Baseline Dispatch Date].[Date], MyCalendar[Date].[Date] <=RELATED('Sub Project w Activities'[ACTUAL_START].[Date])), MyCalendar[Working Day]))) You're counting number of rows within filtered MyCalendar table you have created where your date range is (in my example) between Latest Baseline Dispatch Date and Actual Start and it only counts the ones that are True in Working Day calendar. Hope this helped someone out there! Thanks Kasia
