[{"id":"ch02lvl1sec12","contentType":"text","title":"Dealing with Dates and Calendars","content":"

Dealing with Dates and Calendars

It is a rare business scenario and data model that does not have something related to dates at its core. So much of the business world deals with dates. People work on certain dates and not others. Deadlines are due on specific days. Bonuses and key performance indicators are dependent upon what week, month, or quarter sales occur. This chapter is all about working with dates, weeks, months, quarters, and years.

This chapter comprises the following recipes:

Using time intelligence
Creating quarters
Calculating leap years
Determining day and working day numbers in a year
Determining date of the day number of a year
Finding week start and end dates
Finding working days for weeks, months, quarters, and years
Constructing a sequential week number
Computing rolling weeks
Replacing Excel's NETWORKDAYS function
Working with date intervals
Exploiting...

","url":"https://subscription.packtpub.com/book/data/9781839217074/2","metaDescription":"","titleSlug":"dealing-with-dates-and-calendars"},{"id":"ch02lvl1sec13","contentType":"text","title":"Technical requirements","content":"

Technical requirements

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

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec13/technical-requirements","metaDescription":"","titleSlug":"technical-requirements"},{"id":"ch02lvl1sec14","contentType":"text","title":"Using time intelligence","content":"

Using time intelligence

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.

Getting ready

To prepare for this recipe, perform the...

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec14/using-time-intelligence","metaDescription":"","titleSlug":"using-time-intelligence"},{"id":"ch02lvl1sec15","contentType":"text","title":"Creating quarters","content":"

Creating quarters

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

...

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec15/creating-quarters","metaDescription":"","titleSlug":"creating-quarters"},{"id":"ch02lvl1sec16","contentType":"text","title":"Calculating leap years","content":"

Calculating leap years

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.

Getting ready

To prepare for this recipe, perform the following steps:

Create a table using the following formula:

R03_Years = GENERATESERIES(1899,3000,1)

How to do it...

To implement this recipe, perform the following steps:

Create the following columns in the R03_Years...

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec16/calculating-leap-years","metaDescription":"","titleSlug":"calculating-leap-years"},{"id":"ch02lvl1sec17","contentType":"text","title":"Determining day and working day numbers in a year","content":"

Determining day and working day numbers in a year

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

Getting ready

To prepare for this recipe, perform the following steps:

Create a table using the following formula:

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

How to do it...

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec17/determining-day-and-working-day-numbers-in-a-year","metaDescription":"","titleSlug":"determining-day-and-working-day-numbers-in-a-year"},{"id":"ch02lvl1sec18","contentType":"text","title":"Determining date of the day number of a year","content":"

Determining date of the day number of a year

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

Getting ready

To prepare for this recipe, perform the following steps:

Create a table using the following formula:

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

In this table, create the following column:

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

How to do it...

To implement this recipe, perform the following steps:

Create a column...

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec18/determining-date-of-the-day-number-of-a-year","metaDescription":"","titleSlug":"determining-date-of-the-day-number-of-a-year"},{"id":"ch02lvl1sec19","contentType":"text","title":"Finding week start and end dates","content":"

Finding week start and end dates

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.

Getting ready

To prepare for this recipe, perform the following steps:

Create a table using the following formula:

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

Create a column in this table using the following formula:

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

The Weekday column is simply for reference and checking; it is not a required part of the recipe.

...

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec19/finding-week-start-and-end-dates","metaDescription":"","titleSlug":"finding-week-start-and-end-dates"},{"id":"ch02lvl1sec20","contentType":"text","title":"Finding working days for weeks, months, quarters, and years","content":"

Finding working days for weeks, months, quarters, and years

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

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec20/finding-working-days-for-weeks-months-quarters-and-years","metaDescription":"","titleSlug":"finding-working-days-for-weeks-months-quarters-and-years"},{"id":"ch02lvl1sec21","contentType":"text","title":"Constructing a sequential week number","content":"

Constructing a sequential week number

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.

Getting ready

To prepare for this recipe, perform the following steps:

Create a table using the following formula:

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

How to do it...

To implement this recipe, perform the following steps:

Create the following column...

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec21/constructing-a-sequential-week-number","metaDescription":"","titleSlug":"constructing-a-sequential-week-number"},{"id":"ch02lvl1sec22","contentType":"text","title":"Computing rolling weeks","content":"

Computing rolling weeks

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

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec22/computing-rolling-weeks","metaDescription":"","titleSlug":"computing-rolling-weeks"},{"id":"ch02lvl1sec23","contentType":"text","title":"Replacing Excel's NETWORKDAYS function","content":"

Replacing Excel's NETWORKDAYS function

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.

Getting ready

To prepare for this recipe, perform the following steps:

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

Created Date	Finished Date
11/17/2019	12/29/2019
12/15/2019	1/13/2020
1/12/2020	3/30/2020

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

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec23/replacing-excel-s-networkdays-function","metaDescription":"","titleSlug":"replacing-excel-s-networkdays-function"},{"id":"ch02lvl1sec24","contentType":"text","title":"Working with date intervals","content":"

Working with date intervals

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.

Getting ready

...

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec24/working-with-date-intervals","metaDescription":"","titleSlug":"working-with-date-intervals"},{"id":"ch02lvl1sec25","contentType":"text","title":"Exploiting alternatives to DAX's time intelligence","content":"

Exploiting alternatives to DAX's time intelligence

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

","url":"https://subscription.packtpub.com/book/data/9781839217074/2/ch02lvl1sec25/exploiting-alternatives-to-dax-s-time-intelligence","metaDescription":"","titleSlug":"exploiting-alternatives-to-dax-s-time-intelligence"}]

Color	Value
Red	0
Green	1
...

DAX Cookbook

By : Gregory Deckler

DAX Cookbook

By: Gregory Deckler

Overview of this book

Related Content you might be interested in

Current Title:

DAX Cookbook

Hands-On Business Intelligence with DAX

Extreme DAX

Learn Power BI