DAX for Power BI Part 6.1 - Introduction to Time Intelligence Functions

By Andrew Gould

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can join this channel to get access to perks:

This video shows you how to get started with time intelligence functions in DAX. You'll learn how to make Power BI create a date table for each date column in your data model automatically. You'll also learn what the auto date table contains and why it's important for the operation of the time intelligence functions. You'll see how to display different levels of a date hierarchy and how to use the DateAdd function to modify the filter context applied to a measure to compare a calculation with past and future dates. Finally, you'll learn how to use the SamePeriodLastYear function as a shorthand for a specific use of the DateAdd function.

Chapters
00:00 Topic list
00:50 The Auto Date Time Setting
02:53 What the Auto Date Table Contains
05:55 Grouping a Measure by Years
07:28 The DateAdd Function
11:18 Showing Different Levels of the Hierarchy
14:02 Comparing to the Previous Year
14:50 Excluding Blanks
16:48 Comparing with the Previous Quarter
18:32 Using the IsFiltered Function
23:41 Comparing the Previous Month Using Variables
29:05 Using a Specialised Time Intelligence Function

[pon]

Thank you for sharing your knowledge. For the time being, I am spending time on other endeavors, but I look forward to the day that I will be going through these tutorial videos.

[kiendra]

Yoooo wise owl got the platinum skin 🤯🤯😳😳😳🔥🔥🔥🔥

[hadibq]

Nice precise examples of using the dates in filters!! Thanks! very well done Andrew! a suggestion please: would it be possible to have a link in the description to the full playlist, so one can easily go to the playlist from a randomly found video. yt doesn't handle this yet, guess it's a good feature to add :)

[ArchStanton]

Hi Andy, I have some code that counts the number of cases that are over 'x' amount of days old that works.
I have a second measure that tries to calculate the value of 'x' a month ago but it just gives me a - - result (its not a BLANK and its not empty).

Would it be possible to send you the code for you to see if there are any glaring mistakes in it? If so, could I paste it here or is there another way to send you it?
I have added comments to the code to help explain what's what.
Thanks

[bretaddison]

I find the column totals confusing on previous quarter and previous year. How should they be understood ?

[ArchStanton]

Hi Andy, I have a Matrix that shows various Time Intelligence based figures and one of them is the change in volume of Cases Month on Month as a %. Cases MoM% is a Measure (see below) that is, as it suggests, the variance as a % on the previous month. How do I stop the Variance showing on Year sub-total level? Is there anything that can be changed in the code below that would just show the % next to the months and not the Year?

Cases MoM% =
VAR ActiveCase = [YTD Active Cases]
VAR ActiveCasesPM = [Previous Mth]
VAR Result =
IF(
[YTD Active Cases] <> 0 && [YTD Active Cases PY] <> 0,
DIVIDE(ActiveCase - ActiveCasesPM, ActiveCasesPM)
)
RETURN
Result

I've been looking for a solution everywhere for this and the easy way out is to change the colour of the font and essentially 'blank' the value out. I want to know how to do this through DAX.


Fingers crossed its straightforward!

[pabeader]

Too bad that it isn't really Time intelligence. Should have been called Period Intelligence or maybe Date Intelligence. I wish someone would figure out haw to actually do Time Intelligence. Things like same period last hour, or Hour over Hour. That would be Time Intelligence.