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.
Anonymous
I created the two tables but got this error when trying to create a relationship:
Any ideas?
SI Project Report is the original table. Thanks!
Wrong tables. You need a relationship between WorkSchedule and DateTable, or whatever name you've given your regular time intelligence date table. Not DateRange though. DateRange is really only there for the purpose of generating WorkSchedule.