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

 

  • 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?

  • Z4m's avatar
    Z4m
    Frequent Visitor
    10 years ago

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