By: Logan Riebel
Here is a link to a YT video where I walkthrough the whole process:
The KPI visualization is an essential part of any Power BI dashboard or report. The power of the KPI is to not only show where we are currently at, but also to give us context into how we are performing with regards to the past.
Example of a KPI:
By making a KPI that dynamically updates to filters, we can not only know how our most important business metrics are performing in the present, but also if we are heading in the right direction.
One problem that I ran into when first using the KPI visual was comparing data across years. This issue was mainly caused by the “Trend Axis” field of the KPI visual.
In this article, I will break down the process of creating a dynamic KPI visual that helps you get the most out of your Power BI dashboards and reports.
Step 1: Identify what metrics are most important for your business
When building a KPI visual, it is important to understand what metrics determine your businesses performance output. Hence, Key Performance Indicator. Examples of KPIs include Website Users, Conversions, and Revenue. For my example, I will be using leads from Salesforce as my Key Performance Indicator.
Step 2: Setup your Date Table to Create a Previous Comparison Date Range to Compare Your KPI to.
Setting up your Date table properly is key in leveraging Power BI’s time intelligence functions. The first step in creating a Date table is defining the range of time that you want the table to stretch. For my report, I want to compare 2019 data to 2020 (present) data. To set up my date table I use the CALENDAR DAX function.
Date_tbl = CALENDAR(DATE(2019,1,1),TODAY())
This function creates a calendar that starts on January 1st, 2019 and goes until today (July 13th, 2020 in this case). This function will allow me to build a slicer that filters my Salesforce data.
I like to set the Date field’s data type to be *3/14/2001. I like this form because it updates based on region (i.e. American date vs. European date) and also trims the date down to a smaller sized column.
The next step in setting up your Date table to properly use the KPI card to compare across different years is to pull out the Year from the “Date” column into its own “Year” column. To do this, use the YEAR function in DAX.
Year = YEAR(Date_tbl[Date])
You should set this field’s data type to Whole Number.
Now, this next function is critical to setting up your Trend axis portion of the KPI visual. You will need to make a “Start of Century” column based on your “Year” column. The trick is to use the SWITCH function in DAX to make the KPI group all of your year functions into 1 bucket (Century).
Start of Century = SWITCH(
You need to add future years in this function to make sure that the KPI visual will continue to work after the current year. I future proofed this report for 7 years (until 2027), but you can add more if you would like to.
Set this field’s data type to Whole Number.
Next, use the FIRSTDATE DAX function to create a measure that defines the start of the current period that my slicer is selecting.
Start of Period = FIRSTDATE(Date_tbl[Date])
Next, use the LASTDATE DAX function to create a measure that defines the end of the current period that my date slicer is selecting.
End of Period = LASTDATE(Date_tbl[Date])
Next, use the CALENDAR DAX function to create a measure that defines the date range that the slicer is selecting.
Date Range = CALENDAR([Start of Period],[End of Period])
Now use the DATEDIFF DAX function to create a measure that calculates the amount of days in the Current Date Range Period that the slicer is selecting.
Days in This Period = DATEDIFF([Start of Period], [End of Period],DAY)
Now that you have all my measures that define the current time period, you need to create measures that define the previous/comparison period.
The first measure to set up is to calculate the end of the previous period. Use a combination of the LASTDATE and DATEADD functions to create this measure.
End of Previous Period = LASTDATE(DATEADD(Date_tbl[Date],-1*[Days in This Period],DAY))-1
It is key to add the -1 at the end so that the Current Period and Previous Period date ranges have no overlap.
The second measure to set up is the calculation of the start of the previous period. Use a combination of the FIRSTDATE and DATEADD functions to create this measure.
Start of Previous Period = FIRSTDATE(DATEADD(Date_tbl[Date],-1*[Days in This Period],DAY))-1
It is key to include the -1 at the end so that you account for the -1 in the End of Previous Period measure.
The last measure to set up is similar to the Days in This Period measure except it uses the Start and End of previous period measures.
Days in Previous Period = DATEDIFF([Start of Previous Period],[End of Previous Period],DAY)
Step 3: Creating a Relationship Between the Date table and a Data table
By creating a relationship between the Date table and a Data table, it allows you to utilize the “Date” field of your date table to filter visuals across your report. The best relationship to use for this scenario is a One to Many relationship. On the one side of the relationship you will be using the “Date” field on your Date table. This is because the CALENDAR function creates a table that has unique date values. The many side of the relationship will be the “Date” field in your Data table. This is because there are many non unique dates in your Data table (i.e. you can receive multiple leads on 1 date).
Step 4: Create a Measure that Calculates the Previous/Comparison Period of the KPI
By creating a Measure using the CALCULATE, DISTINCTCOUNT, DATESBETWEEN, and ALL functions in DAX you are able to give the KPI visual the “Target goals” field.
Previous Period Leads = CALCULATE(
DISTINCTCOUNT(‘Leads Data'[Company / Account]),
[Start of Previous Period],
[End of Previous Period]),
Step 4: Creating your Filter Slicers & KPI visual
The first step in this process is to create your desired filters. By creating a Slicer visualization and placing the “Date” field from the Date table into the “Field” category and using the slicer header to use the “Between” format, you can have a Date Filter that looks like this (I also removed the Slider control). I also add a Multi-row Card visual to show the Start of Previous Period and End of Previous Period measures (this helps with the user experience). The final filter I create is a slicer that filters on “Acquisition Channel” from Salesforce. This is a field that the sales reps put in and defaults to “-”.
Now, I finally am able to create my KPI visualization. For the “Indicator” field I use the Distinct count of “Company/Account” from SF. For the “Trend axis” field I use the “Start of Century” Column. Last, I use the “Previous Period Leads” measure under the “Target goals” field. This creates a KPI visualization that shows the correct math when comparing across different years.
You may be asking the question “Logan, why must I use the “Start of Century” column for the “Trend axis” when I already have the “Year” column?”
You must use the “Start of Century” column to group all of your years. Originally, when I was creating KPI cards I would use the “Year” column for the Trend axis field. This solution works great if you are only comparing data across 1 year in your report or dashboard. However, when comparing across different years Power BI does not recognize a change in the KPI value when you have the “Trend axis” field set to Year.
Here are some examples of this:
First, I will have my KPI “Trend axis” set to Year and show you that the KPI visual works great when the Date Filter is set to dates within the same year (2020 in this case).
However, when selecting a start of the current period that is in the previous year (2019) the KPI visual will not include data from 2019 when the “Trend axis” is set to “Year”.
Here is what the KPI visual says when using “Start of Century” as the “Trend axis”
This method also gives us the correct number when we select dates within the same year (June 2020)
I hope you found this article on how to use the KPI visual to compare across different years useful!
Once again, here is a link to a YT video where I walkthrough the whole process: