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

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.