The following are required to complete all of the recipes in this chapter:

• Power BI Desktop
• A GitHub repository: https://github.com/PacktPublishing/DAX-Cookbook/tree/master/Chapter02

Time intelligence in DAX is somewhat of a misnomer. Time intelligence actually does not deal with time in the sense of hours, minutes, seconds, and so on. Instead, time intelligence would be better off being called date intelligence or calendar intelligence, because the time intelligence functions in DAX really deal with dates. Hence, if you are looking for calculations involving time in the sense of hours, minutes, and seconds, proceed to the next chapter. Otherwise, this recipe will demonstrate how to use the time intelligence functions in DAX to perform calculations related to things such as year-over-year, month-over-month, and quarter-to-date.

To prepare for this recipe, perform the...

You may consider it somewhat silly to have a recipe for determining the quarter of a date since quarter is included in the default hierarchy for a date. However, many organizations do not adhere to a standard calendar quarter. For example, Microsoft's fiscal calendar runs from June to May. Other organizations start and stop their quarters on specific dates of the year. This quarter calculation can be used with standard quarterly calendars as well as non-standard quarterly calendars. It is easily modified to fit just about any custom quarterly schedule you can imagine. The quarter table that we will create in this recipe has the following quarters:

• Q1: August 15 – November 14
• Q2: November 15 – February 14
• Q3: February 15 – May 14
• Q4: May 15 – August 14

A leap year is defined as a year that is evenly divisible by 4, but not evenly divisible by 100 unless that year is also evenly divisible by 400. Confused? This is why this recipe exists! This recipe calculates whether or not a year is a leap year and the number of days in a year.

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R03_Years = GENERATESERIES(1899,3000,1)

To implement this recipe, perform the following steps:

1. Create the following columns in the R03_Years...

There are many business scenarios where you need to know the sequential day number of the year or the sequential working day number of the year. For example, February 1 would be the 32nd day of the year (since January has 31 days). This recipe provides calculations for finding this sequential day number within a year as well as the sequential working day number within a year (excludes weekends).

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R04_Calendar = CALENDAR(DATE(2018,1,1),DATE(2022,12,31))

This recipe comes in handy if you know the day number of a year and wish to determine the actual date of that day.

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R05_Calendar = CALENDAR(DATE(2018,1,1),DATE(2022,12,31))

2. In this table, create the following column:

DayNoOfYear = ('R05_Calendar'[Date] - DATE(YEAR('R05_Calendar'[Date]), 1, 1 )) * 1 + 1

To implement this recipe, perform the following steps:

1. Create a column...

Many organizations have the concept of week starting and week ending, especially when dealing with time tracking or reporting. This recipe provides a way to calculate the week start and end dates for any date within a calendar year.

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R06_Calendar = CALENDAR(DATE(2018,1,1),DATE(2022,12,31))

2. Create a column in this table using the following formula:

Weekday = FORMAT([Date],"dddd")

Finding the first and last days of a month in DAX is fairly straightforward. The starting day of a month is always 1 and DAX includes a handy EOMONTH function to return the last day of a month. Similarly, finding the first and last days of a year is extremely straightforward considering that years always begin on January 1 and end on December 31. Nevertheless, things become trickier when attempting to find the first and last day of a week and become much, much trickier when trying to identify the first working day of a week, month, or year.

However, since most businesses have the concept of a work week that includes work days and non-work days, it is often important to be able to identify the first and last working days of weeks, months, and years. Luckily, this recipe shows exactly how to accomplish finding the first...

While perhaps not obviously useful on its own, there are many circumstances where having a sequential number across weeks, months, quarters, and years can facilitate other calculations. This recipe demonstrates how to construct a sequential week number across years.

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R08_Calendar = CALENDAR(DATE(2018,1,1),DATE(2022,12,31))

To implement this recipe, perform the following steps:

1. Create the following column...

There are a number of business analysis calculations, such as forecasting, that often require an analysis of a sliding scale of the data from previous (complete) date periods. For example, it is fairly common that the last 3 complete months' worth of data is used to forecast the current month. In other words, if today's date is April 6, 2020, then the dates desired would be from January 1, 2020 to March 31, 2020. These sliding scales must be able to cross year boundaries as well as ensure that the date ranges calculated are exact. Being off by even a single day can sometimes greatly affect forecasts.

While Power BI has a DAX rolling average quick measure, this measure only works with days, months, quarters, and years. The reason is that DAX's time intelligence generally does not support weeks, and for good reason. Dealing with weeks tends...

Excel has a NETWORKDAYS function that calculates the number of days between two dates minus weekends and holidays. While DAX has a DATEDIFF function that calculates the number of days between two dates, the DATEDIFF function does not account for subtracting weekends and holidays. This recipe is a recreation of Excel's NETWORKDAYS function in DAX.

To prepare for this recipe, perform the following steps:

1. Use an Enter Data query to create a table called R10_Table with the following columns and rows:

2. Ensure that both columns are set to have a data...

There are many scenarios involving dates where you have two or more sets of dates as items or transactions move through a process. It is often desirable to know how many items or transactions are in one state or another at any given time. Unfortunately, this is something that is not straightforward to present in a report given that, with just the raw data, it is difficult, if not impossible, to depict how and when items and transactions transitioned from one state to another.

This recipe presents a simple scenario where help tickets are opened on one date and then closed. This recipe demonstrates how to see how many tickets are in process (open) at different date intervals, such as by year, month, and day.

The problem with DAX's time intelligence functions, other than the fact that they are ill-named, is that, well, they really are not all that intelligent. You see, all of these functions proceed from the same basic assumption that everything works on the basis of a standard calendar year. However, this is not the case for many businesses, including Microsoft! Thus, this underlying assumption of a standard calendar year makes any DAX time intelligence functions dealing with quarters particularly useless. In addition, many of the time intelligence functions are not supported in DirectQuery mode, which, again, makes them entirely useless in such scenarios.

But fear not, as I have mentioned, the vast majority of DAX's time intelligence functions are really not all that intelligent. In fact, they are really simple shorthand...