Power BI is a powerful business intelligence tool that allows users to visualize and analyze data from a variety of sources. One of the key features of Power BI is the ability to create custom calculations using Data Analysis Expressions (DAX), a formula language used in Power BI and other Microsoft products. In this blog post, we will provide an introduction to DAX and demonstrate some practical examples of how it can be used in Power BI. We will also include two informative YouTube videos that explain how to create calculated columns in DAX.

For this exciting series, John Analoh, a Growth Data Analyst will be your instructor.

We assume a very practical approach here by exploring the COVID-19 dataset on infections (confirmed cases) and deaths by country. It's timeseries data and the expectation is that you would have the skills to analyse effectively any given dataset using statistical measures and develop a compelling dashboard with useful insights.

Dataset

You can download the dataset for the practical examples here.

1. Create a calculated column in DAX

In this video, we will learn how to create a calculated column using DAX in Power BI. A calculated column is a new column that is based on an expression or formula that you define. This can be useful when you want to add new data to an existing table, or when you need to perform calculations that are not already included in your dataset. The video will provide step-by-step instructions on how to create a calculated column using DAX.

2. Create conditional calculated columns in DAX

In this video, we will learn how to create conditional calculated columns in DAX. This involves using logical expressions to specify conditions that must be met for a calculation to be performed. For example, you may want to create a new column that calculates the average sales for each region, but only for products that have a sales value above a certain threshold. The video will demonstrate how to create conditional calculated columns using DAX in Power BI.

3. Calculate the percentage (%) of total

In this section, we will explore how to calculate the percentage of total in DAX. This involves dividing the value of each item in a column by the total value of all the items in the same column, and then multiplying the result by 100 to get a percentage. For example, you may want to calculate the percentage of total sales for each product category. We will show you how to use the DIVIDE and SUMX functions in DAX to achieve this calculation in Power BI.

4. Use the CALCULATE function with filters

In this section, we will show you how to use the CALCULATE function with filters in DAX. The CALCULATE function is a powerful tool that allows you to modify the filter context of a calculation in Power BI. By using filters, you can specify which rows or columns of data to include or exclude from a calculation. For example, you may want to calculate the sales for a specific product category and date range. We will demonstrate how to use the CALCULATE function with filters in DAX to achieve this calculation in Power BI.

5. Create a date table

In Power BI, creating a date table is an essential step in building a data model for time-based analysis. The date table provides a reference for dates and allows for easy filtering and aggregating of data by date. There are two common ways to create a date table in DAX: using CALENDAR() and using GENERATE() and ROW().

The CALENDAR() function is a built-in DAX function that generates a table of dates between two specified dates. For example, the following DAX formula creates a date table from January 1st, 2020 to December 31st, 2021:

DateTable = CALENDAR("2020-01-01", "2021-12-31")

6. Use GENERATE and ROW functions to create a date table

In addition to using CALENDAR() or GENERATE() and ROW() separately to create a date table, it's also possible to use these functions together. This can provide the benefits of both approaches - the simplicity of the CALENDAR() function and the flexibility of the GENERATE() and ROW() functions.

For example, the following DAX formula creates a date table from January 1st, 2020 to December 31st, 2021, with additional columns for week number and month name:

DateTable = 
ADDCOLUMNS(
    CALENDAR("2020-01-01", "2021-12-31"),
    "Week Number", WEEKNUM([Date]),
    "Month Name", FORMAT([Date], "MMMM")
)

In this formula, the CALENDAR() function creates the initial date table, and the ADDCOLUMNS() function adds the additional columns for week number and month name. The WEEKNUM() function calculates the week number for each date, and the FORMAT() function formats the date as the full month name.

In comparison, the CALENDAR() function and consequently adding the columns you need is a simpler and more straightforward way of creating a date table. However, using GENERATE() and ROW() can provide more flexibility in generating a custom date table, such as including additional columns or generating non-contiguous dates. Ultimately, the approach used in creating a date table depends on the specific requirements of the analysis.

Time Intelligence Functions

7. Extract PREVIOUS DAY, PREVIOUS MONTH

The DAX functions PREVIOUSDAY() and PREVIOUSMONTH() allow you to extract the values for the previous day and previous month, respectively, based on a given date column. These functions can be incredibly useful for analyzing time series data and comparing performance over different periods.

For example, suppose you have a sales dataset with a date column, and you want to compare the sales figures for the previous day and the previous month with the current day's sales. You can use the PREVIOUSDAY() and PREVIOUSMONTH() functions to extract the sales values for those periods, and then use them to calculate the percentage change in sales over time.

8. Calculate percentage change and handle errors in DAX

The DAX functions PREVIOUSDAY() and PREVIOUSMONTH() allow you to extract the values for the previous day and previous month, respectively, based on a given date column. These functions can be incredibly useful for analyzing time series data and comparing performance over different periods.

For example, suppose you have a sales dataset with a date column, and you want to compare the sales figures for the previous day and the previous month with the current day's sales. You can use the PREVIOUSDAY() and PREVIOUSMONTH() functions to extract the sales values for those periods, and then use them to calculate the percentage change in sales over time.

9. Calculate PREVIOUS QUARTER value in DAX

In addition to PREVIOUSDAY() and PREVIOUSMONTH(), DAX also provides a function called PREVIOUSQUARTER(), which returns the value for the previous quarter based on a given date column. This function can be useful when analyzing quarterly data and comparing performance over different quarters.

For example, suppose you have a dataset with quarterly sales figures, and you want to compare the sales for the previous quarter with the current quarter. You can use the PREVIOUSQUARTER() function to extract the sales value for the previous quarter, and then use it to calculate the percentage change in sales over time.

10. Calculate month-to-date, year-to-date values in DAX

The TOTALMTD and DATESMTD functions in DAX are useful for calculating month-to-date. TOTALMTD returns a running total of the specified measure over a period starting from the beginning of the current month and ending with the current date. On the other hand, DATESMTD returns a table containing all dates from the start of the current month up to and including the current date. These functions are valuable for tracking sales, expenses, or any other time-based metric throughout a specific period.

11. Modify date calculations with DATEADD

The DATEADD function in DAX allows you to modify date calculations by adding or subtracting days, months, or years from a specific date. This function is useful when you need to create custom time periods, such as a 14-day period or a 10-month period. It's also handy when calculating rolling averages, year-over-year comparisons, and other time-based metrics.

12. Filter data with the PARALLELPERIOD function in DAX

The PARALLELPERIOD function in DAX allows you to filter data across a specific time period, such as the previous quarter or year. This function is useful for comparing metrics across different time periods and can be combined with other DAX functions to create more complex time intelligence calculations. By specifying a date column and the number of periods to go back or forward, PARALLELPERIOD returns a table of dates that correspond to the same time period in the previous or future year, quarter, month, or week.

This brings us to our conclusion of this short series on a practical introduction to DAX using the COVID-19 dataset. This is only the start of your data journey.

For a more comprehensive approach to learning the fundamentals of data analytics in Power BI, check out our novel Power BI courses here.