Forum Discussion
Time Intelligence Calculations
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.
Make sure you are using a date table. See link:
Also, give us an example of your DAX so that we can help you troubleshoot it.
I was trying to create the table using the CALENDARAUTO Dax function.
Rsanjuan If you're trying to count things with a start date and an end date you pretty much can't do any normal time intelligence. Time intelligence works against single events like a sale. You can look at sales records and tell exactly what date each sale happened on, but a job with a start and end date didn't happen on any one date so you can't have a normal relationship between the two tables. You still need a date table, but the relationships and formulas are totally different. Fortunately just about all my work uses start and end dates, so if that is what you're trying to do I can give you several different options for how to approach the problem, but you'll need to give some details about how your data is structured and what you want to do with it.
Anonymous Thank you for the reply. Yes, that is what I'm trying to do actually. I would like to take the total sales within a given time period, and compare it to another time period.
