Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KHorseman
Community Champion
Community Champion

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

11 REPLIES 11
Z4m
Frequent Visitor

@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
)PBIError.png

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Z4m
Frequent Visitor

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

Z4m
Frequent Visitor

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
Super User
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@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

 

Generate Schedule Table.png

 

KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.