Time Intelligence Calculations

[Rsanjuan]

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!

[kcantor]

Make sure you are using a date table. See link:

https://support.office.com/en-us/article/Time-Intelligence-in-Power-Pivot-in-Excel-016acf7b-9ded-411...

Also, give us an example of your DAX so that we can help you troubleshoot it.

[Rsanjuan]

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

[KHorseman]

@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.

[Rsanjuan]

@KHorseman 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.  

[KHorseman]

@Rsanjuan There are two ways to do this. One way is to use a bunch of CALCULATE and FILTER conditions and a disconnected date table, but this means you cannot use any standard time intelligence functions and you have to fake them yourself. This gets complicated quickly and is hard to debug because you end up with measures that are 20 lines of code. It's frequently what I use but I don't like it, so I'm going to recommend a newer way I've found: the easier way is to transform your data so that it is represented as if it were transactional rather than durational. This was the solution I was looking for in a similar situation to yours a while back, and @OwenAuger came up with the formula to make it work.

If you have a table with rows that each have only one significant date, you can hook it up to a date table and use standard time intelligence functions. So to do this you will create a second table. In it, rather than having a single row for each job with a start and end date, each job would have multiple rows, one row for each date during its active period between start and end. Then you can link that new table up to a date table and do standard time intelligence calculations using a distinctcount of either unique job ID or unique person ID, depending on which of those is appropriate for your scenario.

Click on the Modeling tab in Power BI and hit New Table. We'll call it WorkSchedule. I'm going to have to assume that the table in your earlier screenshot has some unique job ID or employee ID column. I'm going with employee ID and further assuming that your screenshotted table is named JobTable.

WorkSchedule = 
SUMMARIZE (
    GENERATE (
        JobTable,
        CALCULATETABLE (
            VALUES ( DateTable[Date] ),
            DATESBETWEEN ( DateTable[Date], JobTable[NewJob.Job Start Date], JobTable[NewJob.Job End Date] )
        )
    ),
    DateTable[Date],
    JobTable[EmployeeID]
)

Now you have a table that looks like a normal transactional fact table. Like retail sales for instance, where each sale at the register has a row, and that row has a single date that it happened on. That's the structure expected for time intelligence, not the start date / end date structure you have. In this case each day that an employee worked has a single date. If you connect that date column to a date table, you can write formulas like

WorkingCount = DISTINCTCOUNT(WorkSchedule[EmployeeID])

Working Month to Date = TOTALMTD( [WorkingCount], DateTable[Date])

Make sense? If you haven't already, check out the other thread I linked to above. It gives some examples of a dataset and how it's all meant to behave.

[KHorseman · Accepted solution]

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.

[Rsanjuan]

@KHorseman

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!

[KHorseman]

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.

[Rsanjuan]

@KHorseman  This makes a lot of sense.  Thank you so much for your help!! It was very thorough and completely understand the logic.