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