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