Generate a schedule table

[KHorseman]

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?

[Z4m]

@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]

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]

Thanks again @OwenAuger for all the help. I will try to update my request as solved....

[Z4m]

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?

[OwenAuger · Accepted solution]

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]
)

[KHorseman]

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]

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]
)

[KHorseman]

I think it's that first step that I was having trouble picturing. So let me try rephrasing your explanation to see if I understand.

If I could see the output of that first version of the GENERATE() statement, it would look like my existing BookedWork table, except that every row with a start and end date would be repeated multiple times, once for each date between the start and end date? And I guess there would be a new Date column added that has those dates? In other words after step 1 it's already the same basic structure as the desired final result, except it still has all the other columns from BookedWork and the rows are by date instead of by week. Right?

So step 2 reduces those rows from dates to weeks, and step 3 reduces the columns down to only week and EmployeeID. Am I on the right track?

[Sean]

@KHorseman as @OwenAuger said you can see each step with => https://daxstudio.codeplex.com/

Here are the Steps copied from Dax Studio and sorted in Excel

[KHorseman]

Like GENERATE(), Dax Studio is another thing I haven't quite learned how to use yet. 😛 But thanks for the breakdown @Sean. That helps.

I just kind of like talking through it conceptually so I can get a better mental model of what's going on when I want to use the same function for a completely different use case. I've generalized what for instance FILTER(ALL(... means well enough that I can always picture what it's doing in anyone's formula, but there are still a few of these table-returning functions that I can't quite imagine as well.

[Sean]

@KHorseman Yes I definetely like to do the same - take my time to break down and see all steps as well.

I was just looking for some info on GENERATE( ) and found this...

http://www.powerpivotpro.com/2012/09/gantt-reworked-with-addcolumns-filter-generate-and-summarize/

Have not had time to explore it yet - but thought you may want to see it too

There's also a Gantt Custom Visual (on my List)

https://app.powerbi.com/visuals/show/Gantt1448688115699

Anyway as always a great solution by @OwenAuger