Forum Discussion

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

calculate data for weekdays in future date

in our business we have a feature that lets users book a timeslot and weekday to recieve their service (weekly or BiWeekly) , for example ,user X booked his service to be  Friday 10:00 AM Weekly , user Y has booked his service to be every Tuesday 1:00 PM  BiWeekly

So user X will recieve his service Friday 10:00AM (Every Week),  and user Y will recieve his serive Tuesday 1:00 PM (Every Two Weeks)

 

the request is :

create a matrix for every future date that shows how many users have booked a specific timeslot , ex:

 

 tuesady 07-06-2021Wednesday 07-07-2021Thursday  07-08-2021Friday  07-09-2021Saturday  07-10-2021Sunday 07-11-2021Monday 07-12-2021tuesady 07-13-2021Wednesday 07-14-2021Thursday  07-15-2021Friday  07-16-2021Saturday  07-17-2021Sunday 07-18-2021Monday 07-19-2021tuesady 07-20-2021Wednesday 07-21-2021Thursday  07-22-2021Friday  07-23-2021   
10:00 AM   1 User      1 User      1 users   
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 datefrequency
124Tusday6:00 PM6/27/2021weekly
567Friday11:00 AMNULLweekly
566Sunday4:00 PMNULLbiweekly
433Friday3:00 PMNULLbiweekly

 

so how can i count total users who booked a service each weekday (weekly/biweekly) for dates in the futute?

date calculation
Help appreciated
Help Requested
Weekday calculation

9 Replies

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi lawada , what is the starting date for biweekly frequency case ?

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

      it's like a calender , assume it starts from today , if a  user have a biweekly frequency evey tuesday  10:00 AM then this user should be counted on tuesdy  07/13/2021 10:00 AM -  07/27/2021 10:00 AM- 08/10/2021 10:00 AM and so on .

      • ERD's avatar
        ERD
        Icon for Community Champion rankCommunity Champion
        5 years ago

        lawada ,

        You need to have Date and Time tables.

        I don't know how your real data looks like and if your example table is with the same columns as in the real scenario, so I'm not sure if it's the best approach in your case, but you can try to play with this measure:

        Measure = 
VAR frequency = MAX ( T[frequency] )
VAR countUsers =
    CALCULATE (
        DISTINCTCOUNT ( T[User ID] ),
        CROSSFILTER ( 'Weekday'[Day Name], 'Date'[Day Name], BOTH ),
        CROSSFILTER ( T[Week day], 'Weekday'[Day Name], BOTH )
    )
RETURN
    SWITCH (
        TRUE (),
        frequency = "weekly", countUsers,
        frequency = "biweekly", IF ( ISEVEN ( MAX ( 'Date'[Week of Year] ) ), "", countUsers ),
        ""
    )

        If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.