Forum Discussion

lawada's avatar
lawada
Icon for Helper III rankHelper III
5 years ago

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-2021Wednesday 06-30-2021Thursday  07-01-2021Friday  07-02-2021Saturday  07-03-2021Sunday 07-04-2021Monday 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 PM1 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 IDWeek dayTimeSerive date
124Tusday6:00 PM6/27/2021
567Friday11:00 AMNULL
566Sunday4:00 PMNULL
433Friday3:00 PMNULL

 

so how can i find for each weekday and time the date for it in the future evey month  ?

Date
Date and Time
Help required
help with date
Weekday

1 Reply

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey lawada ,

     

    create a calendar table that is reaching for the future and then do a cross join with your appointment table.

    I personally would maybe do that already in the source as you can re-use it. But it's also possible in Power BI.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis