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

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Like GENERATE(), Dax Studio is another thing I haven't quite learned how to use yet. :P But thanks for the breakdown Sean. That helps.

         

        I just kind of like talking through it conceptually so I can get a better mental model of what's going on when I want to use the same function for a completely different use case. I've generalized what for instance FILTER(ALL(... means well enough that I can always picture what it's doing in anyone's formula, but there are still a few of these table-returning functions that I can't quite imagine as well.