Home Data DAX Cookbook

DAX Cookbook

By Gregory Deckler
books-svg-icon Book
eBook $35.99 $24.99
Print $48.99
Subscription $15.99 $10 p/m for three months
$10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
BUY NOW $10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
eBook $35.99 $24.99
Print $48.99
Subscription $15.99 $10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
  1. Free Chapter
    Dealing with Dates and Calendars
About this book
DAX provides an extra edge by extracting key information from the data that is already present in your model. Filled with examples of practical, real-world calculations geared toward business metrics and key performance indicators, this cookbook features solutions that you can apply for your own business analysis needs. You'll learn to write various DAX expressions and functions to understand how DAX queries work. The book also covers sections on dates, time, and duration to help you deal with working days, time zones, and shifts. You'll then discover how to manipulate text and numbers to create dynamic titles and ranks, and deal with measure totals. Later, you'll explore common business metrics for finance, customers, employees, and projects. The book will also show you how to implement common industry metrics such as days of supply, mean time between failure, order cycle time and overall equipment effectiveness. In the concluding chapters, you'll learn to apply statistical formulas for covariance, kurtosis, and skewness. Finally, you'll explore advanced DAX patterns for interpolation, inverse aggregators, inverse slicers, and even forecasting with a deseasonalized correlation coefficient. By the end of this book, you'll have the skills you need to use DAX's functionality and flexibility in business intelligence and data analytics.
Publication date:
March 2020
Publisher
Packt
Pages
552
ISBN
9781839217074

 

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

Technical requirements

 

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

 

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

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:

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

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

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:

  1. Create a table using the following formula:
R04_Calendar = CALENDAR(DATE(2018,1,1),DATE(2022,12,31))

How to do it...

 

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:

  1. Create a table using the following formula:
R05_Calendar = CALENDAR(DATE(2018,1,1),DATE(2022,12,31))
  1. 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:

  1. Create a column...
 

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:

  1. Create a table using the following formula:
R06_Calendar = CALENDAR(DATE(2018,1,1),DATE(2022,12,31))
  1. 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.

...
 

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

 

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:

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

  1. Create the following column...
 

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

 

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:

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

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

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

...
 

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

About the Author
  • Gregory Deckler

    Greg Deckler is Vice President of the Microsoft Practice at Fusion Alliance and has been a professional technology systems consultant for over 25 years. Internationally recognized as an expert in Power BI, Greg Deckler is a Microsoft MVP for Data Platform and a superuser within the Power BI community with over 100,000 messages read, more than 11,000 replies, over 2,300 answers, and more than 75 entries in the Quick Measures Gallery. Greg founded the Columbus Azure ML and Power BI User Group (CAMLPUG) and presents at numerous conferences and events, including SQL Saturday, DogFood, and the Dynamic Communities User Group/Power Platform Summit.

    Browse publications by this author
Latest Reviews (3 reviews total)
It was what i was expecting
Good information and tips.
Dużo użytecznej wiedzy. Ok
DAX Cookbook
Unlock this book and the full library FREE for 7 days
Start now