Forum Discussion

Rsanjuan's avatar
Rsanjuan
Advocate III
10 years ago
Solved

Time Intelligence Calculations

Hi,

 

I am looking to Calculating YearToDate, MonthToDate, WeekToDate and then comparing it to previous 2 years.  Have been trying the DAX expressions but can't seem to get the syntax working correctly.  If someone could please assist, that would be great!  Thanks!

 

Need Help
  • Anonymous's avatar
    Anonymous
    10 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.

9 Replies

    • Rsanjuan's avatar
      Rsanjuan
      Advocate III
      10 years ago

      I was trying to create the table using the CALENDARAUTO Dax function.

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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.