Power BI Date Conditional Formatting

BY: David Rohlfs

Introduction

In this blog we are going to go over a way to use conditional formatting for dates. This blog will build from the knowledge from another EPM Strategy Blog, Conditional Formatting with DAX. The difference from this blog and that one is that we will be going over the use of dates in our DAX equation.

In this blog we will go over when to use this conditional formatting and a short walkthrough of using this equation with a Date of Birth.

 

When to use it

The way that this equation works makes it so that you can calculate a number of days out from a start or end date. This makes it so that you can use an equation like this in many reports that have deadlines, due dates, age requirements, goals by dates, etc.

If you are building a report for a deadline driven company, this can help to create a report that has depth and illustrates the abilities of Power BI. When you are building a report, the main goal is to develop interest and understanding from an end-user. Conditional formatting does great at achieving this because it allows you to use color in your report and create depth through the formatting.

 

Walkthrough

In this walkthrough we are going to go through an example that highlights when a person is about to turn 21 years old or is already 21 years old. Doing this example will show a near deadline color and a past deadline color. You can easily alter this equation to show more or less ranges based on a date field.

First you will want to show a table or matrix of your data. This is a great common practice because it allows you to check your equation as soon as you build it. Because I am using a Date of Birth (DOB), I will put a name and DOB in my table.

 

Now you can create a new measure to build your equation. To do this you can click the ellipses next to your field name and select New Measure. Note: I usually like to build longer equations in a note document because it lets me save it for later if I don’t finish it all at once.

 

Now we get to build our equation. Because this is a longer and more complex equation, I am going to use variables in my equation. To learn how these work with DAX, you can read EPM Strategy’s blog on DAX Variables.

To start we are going to type out our variables. In this section we are defining our DOB (_DOB), the one-month reminder (_21Remind), and the day the subject will turn 21 years old (_21YO). Both y reminder and 21^st birthday will use similar equations.

 

For our results, I am still creating them in the Variables statement of our equation. This is to reduce the number of measures I have because we will be determining our colors in the Return statement. In both statements I am using an IF () function with a True or False as the return value.

My first result (_Result) will be for the one-month reminder. To create this, we are using Today () to determine if we are in the one-month range before the subject turns 21 years old.

My second result (_Result2) will determine if the subject is over 21 years old. To do this we are just setting today greater than the _21YO equation.

 

Now we are going to make our Return statement. I this we are using a Switch () Function to switch our true return values into the colors we want. This section is very similar to the Conditional Formatting with DAX return statement from another EPM Strategy blog.

 

Now we are going to enter our equation into the conditional formatting for our column.

 

 

 

Once we click ok, this is our result.

 

Conclusion

Using conditional formatting for a date has so many uses for any report and can help to display critical information to any end-user. Because so many jobs are using deadlines, this can help to remind end-users when those deadlines are. Even though conditional formatting can be tedious at points, it is an extremely useful tool when there is important information to display in a table or matrix.

Comments