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 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]
)
Anonymous's avatar
Anonymous
Not applicable
10 years ago

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.

  • OwenAuger's avatar
    OwenAuger
    Super User
    10 years ago

    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]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      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?