Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Generate a schedule table

In one of my data sources I have a payroll table. For each employee it has a row for each date that they worked. I'd like to build something for future booked work. I think I have all the components to build it but I'm not sure how to put it together. I believe it's something to do with the table formulas that I haven't quite managed to wrap my brain around yet like SUMMARIZE and CALCULATETABLE and so forth. Perhaps one of you can show me the way.

 

Here's the plan: I don't care about which actual days of the week people will work. I just want them marked on all weeks in the range of their scheduled work. So where the payroll shows a row for each day for each employee, and it skips dates when they didn't work, I want a row per week per employee (see below).

 

By the way, I know of several ways to get a count of working employees per week without creating this extra table. This table is for other purposes.

 

The basis for this will be two tables:

1) my standard DateTable, which includes of course a continuous [Date] column as well as a [Week] column (which is the date of the last day of the week for each [Date]), and

2) a BookedWork table, which includes [EmployeeID], [StartDate] and [EndDate].

 

DateTable:

Date        Week
5/29/2016    6/4/2016
5/30/2016    6/4/2016
5/31/2016    6/4/2016
6/1/2016    6/4/2016
6/2/2016    6/4/2016
6/3/2016    6/4/2016
6/4/2016    6/4/2016
6/5/2016    6/11/2016
6/6/2016    6/11/2016
6/7/2016    6/11/2016
6/8/2016    6/11/2016
6/9/2016    6/11/2016
6/10/2016    6/11/2016
6/11/2016    6/11/2016
6/12/2016    6/18/2016
6/13/2016    6/18/2016
6/14/2016    6/18/2016

...etc.

 

BookedWork:

EmployeeID    StartDate    EndDate
Emp0015      5/1/2016      6/4/2016
Emp0016      5/1/2016      6/4/2016
Emp0027      6/3/2016      7/9/2016
Emp0028      6/11/2016      8/1/2016
Emp0029      6/18/2016     8/10/2016

...etc.

 

 

And this is the desired output:

 

WorkSchedule:

Week         EmployeeID
6/4/2016     Emp0015
6/4/2016     Emp0016
6/4/2016     Emp0027
6/11/2016   Emp0027
6/11/2016    Emp0028
6/18/2016    Emp0027
6/18/2016    Emp0028
6/18/2016    Emp0029

...

 

Any ideas?

Need Help
  • OwenAuger's avatar
    OwenAuger
    10 years ago

    Hi there,

    Sounds like you want to create this as a DAX calculated table. Here's one way of doing it:

    (Note: I'm assuming no relationship between BookWork and DateTable - might change things slightly if there is a relationship.)

    WorkSchedule = 
SUMMARIZE (
    GENERATE (
        BookedWork,
        CALCULATETABLE (
            VALUES ( DateTable[Week] ),
            DATESBETWEEN ( DateTable[Date], BookedWork[StartDate], BookedWork[EndDate] )
        )
    ),
    DateTable[Week],
    BookedWork[EmployeeID]
)

11 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    10 years ago

    Hi there,

    Sounds like you want to create this as a DAX calculated table. Here's one way of doing it:

    (Note: I'm assuming no relationship between BookWork and DateTable - might change things slightly if there is a relationship.)

    WorkSchedule = 
SUMMARIZE (
    GENERATE (
        BookedWork,
        CALCULATETABLE (
            VALUES ( DateTable[Week] ),
            DATESBETWEEN ( DateTable[Date], BookedWork[StartDate], BookedWork[EndDate] )
        )
    ),
    DateTable[Week],
    BookedWork[EmployeeID]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      That appears to work perfectly. Now to sit down and try to understand exactly what each step in that code does. I have trouble with those functions because I feel like there are some unseen intermediate steps that I can't quite visualize right.

      • OwenAuger's avatar
        OwenAuger
        Super User
        10 years ago

        That's good :)
        With these sorts of formulas I would build them up from inside out, either in DAX Studio or just in Power BI Desktop, with a reduced dataset to see what's going on.

         

        In this case, the sequence I went through was:

         

        1. Add the dates between StartDate & EndDate to the BookedWork table (repeating each row of BookedWork for each date)

        = GENERATE (
        BookedWork,
        DATESBETWEEN ( DateTable[Date], BookedWork[StartDate], BookedWork[EndDate] )
)

        2. Change those dates to weeks instead

         =   GENERATE (
        BookedWork,
        CALCULATETABLE (
            VALUES ( DateTable[Week] ),
            DATESBETWEEN ( DateTable[Date], BookedWork[StartDate], BookedWork[EndDate] )
        )
    )

         3. Create summary table with just weeks and employees:

        = SUMMARIZE (
    GENERATE (
        BookedWork,
        CALCULATETABLE (
            VALUES ( DateTable[Week] ),
            DATESBETWEEN ( DateTable[Date], BookedWork[StartDate], BookedWork[EndDate] )
        )
    ),
    DateTable[Week],
    BookedWork[EmployeeID]
)
  • Z4m's avatar
    Z4m
    Frequent Visitor
    10 years ago

    OwenAuger Please help me, I've tried to achieve the same as the solution below but i receive an error after submitting the DAX code.
    I get the error: "A column specified in the call to function 'DATESBETWEEN' is not of type DATE. This is not supported"

    Similar to the example, i have two tables one named "Matchprofiles" and one named "CALENDAR"

    The matchprofiles constains the hrm_name (name op de employee) and Startdate/Enddate

    Matchprofiles:

    hrm_name    StartDate    EndDate        Value

    emlp007        1-1-2016  10-1-2016        4

    emlp008       1-1-2016   3-1-2016          5

    CALENDAR:
    Date          Weeknumber  Day of Week

    01-01-16      1                     5
    Instead using the week like the example i would like to show the week (number)

    WorkSchedule:

    Week         EmployeeID Value
    1                 emlp007      4
    2                emlp007       4
    1                emlp008       5

    Can you please help me? All collumn containing a Date are in fact of type date so. There aren't any relationships so far between these tables just like it was mentioned. 
    Additionally, i need to add the "value" for every week (this is representing the amount of Day's in that week")

    My Code: 
    WorkSchedule =
    SUMMARIZE (
    GENERATE (
    MatchProfiles;
    CALCULATETABLE ( MatchProfiles;
    VALUES ('CALENDAR'[Date]);
    DATESBETWEEN ('CALENDAR'[WeekNumber]; MatchProfiles[hrm_StartDate];MatchProfiles[hrm_EndDate])
    )
    );
    MatchProfiles[hrm_name]

    I also added a screendump of the error i got when adding the DAX code to the new table definition
    )

    • OwenAuger's avatar
      OwenAuger
      Super User
      10 years ago

      Hi Z4m,

       

      The reason for that particular error is that you have passed 'CALENDAR'[Weeknumber] as the first argument of DATESBETWEEN, not 'CALENDAR'[Date].

       

      There seem to be a few fixes needed including that one:

      1. CALCULATETABLE doesn't need Matchprofiles as the first argument
      2. The first argument of CALCULATETABLE should be VALUES( 'CALENDAR'[Weeknumber] )
      3. The first argument of DATESBETWEEN should be the date column itself, i.e. 'CALENDAR'[Date]
      4. The final arguments of SUMMARIZE need to be all columns you want summarized, so you should add 'CALENDAR'[Weeknumber] and MatchProfiles[Value]

      I wasn't entirely sure how to interpret Matchprofiles[Value] but have assumed you just want this included in the summary table.

       

      The corrected code should look something like this (not worrying about column names in WorkSchedule):

       

      WorkSchedule =
SUMMARIZE (
    GENERATE (
        MatchProfiles;
        CALCULATETABLE (
            VALUES ( 'CALENDAR'[Weeknumber] );
            DATESBETWEEN (
                'CALENDAR'[Date];
                MatchProfiles[hrm_StartDate];
                MatchProfiles[hrm_EndDate]
            )
        )
    );
    'CALENDAR'[Weeknumber];
    MatchProfiles[hrm_name];
    MatchProfiles[Value]
)

       

      • Z4m's avatar
        Z4m
        Frequent Visitor
        10 years ago

        Dear OwenAuger,

        Thanks for your reply i've immediatly added the code and checked for mispelled collumn.
        Unfortunately, I got a different error message now saying; "An invalid numeric representation of a date value was encountered"
        After some search on different forums i've found some posts with persons having the same troubles.
        In some cases the solution was to rule out any invalid date format in the rows.
        Now i've checked both tables for any issues and even filtered the table to ensure no issues are in there, but i still get the message.
        I've tried to send you directly a message about this error because i am kind of new and don't want to fill this thread with wat 'appears' to be maybe a beginners issue...
        Is it possible to send you my pbx file so you can possibly check-out what i am doing wrong?