Forum Discussion
Time Intelligence Calculations
- Anonymous10 years ago
Oh yeah, I forgot that you'll need a second date table, otherwise you'll have a circular dependency. I would recommend not using your normal date table in the WorkSchedule formula. Save your regular time intelligence date table for the relationship with this new WorkSchedule table. Create another custom table:
DateRange = CALENDAR( FIRSTDATE(JobTable[NewJob.Job Start Date]), LASTDATE(JobTable[NewJob.Job End Date]))
...or you could ignore those two date fields and write it between two static dates that you want to set yourself that will cover the range you'll need. Whatever you prefer.
Then your WorkSchedule formula would be
WorkSchedule = SUMMARIZE ( GENERATE ( JobTable, CALCULATETABLE ( VALUES ( DateRange[Date] ), DATESBETWEEN ( DateRange[Date], JobTable[NewJob.Job Start Date], JobTable[NewJob.Job End Date] ) ) ), DateRange[Date], JobTable[EmployeeID] )Then you would create a relationship between WorkSchedule[Date] and DateTable[Date] (your regular date table, not that dummy range we just created) to use for the time intelligence stuff. Everything else is as I described previously.
Oh yeah, I forgot that you'll need a second date table, otherwise you'll have a circular dependency. I would recommend not using your normal date table in the WorkSchedule formula. Save your regular time intelligence date table for the relationship with this new WorkSchedule table. Create another custom table:
DateRange = CALENDAR( FIRSTDATE(JobTable[NewJob.Job Start Date]), LASTDATE(JobTable[NewJob.Job End Date]))
...or you could ignore those two date fields and write it between two static dates that you want to set yourself that will cover the range you'll need. Whatever you prefer.
Then your WorkSchedule formula would be
WorkSchedule =
SUMMARIZE (
GENERATE (
JobTable,
CALCULATETABLE (
VALUES ( DateRange[Date] ),
DATESBETWEEN ( DateRange[Date], JobTable[NewJob.Job Start Date], JobTable[NewJob.Job End Date] )
)
),
DateRange[Date],
JobTable[EmployeeID]
)
Then you would create a relationship between WorkSchedule[Date] and DateTable[Date] (your regular date table, not that dummy range we just created) to use for the time intelligence stuff. Everything else is as I described previously.
Anonymous This makes a lot of sense. Thank you so much for your help!! It was very thorough and completely understand the logic.