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

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