In the world of data visualization and business intelligence, Key Performance Indicators (KPIs) play a pivotal role in monitoring and understanding the health of a business or project. A KPI card is a powerful tool that condenses vital information into a concise, visually appealing format. Today, we’re going to take a deep dive into creating a KPI card, step by step, using a popular data analysis tool.

Step 1: Calculating the Current Year (CY)

The first step is to calculate the current year (CY) based on the ‘Order Date.’ We achieve this by creating a calculated field:

[CY] = {MAX(DATETRUNC(‘year’, [Order Date]))}

This field captures the maximum date, truncating it to the year, thus representing the current year.

Step 2: Identifying Current Year (CY) Entries

Now, we create a field to flag entries within the same year as the current year:

[IS CY] = [CY] = DATETRUNC(‘year’, [Order Date])

This field simplifies the process of filtering data for the current year.

Step 3: Calculating Previous Year (PY) Sales

To calculate the sales for the previous year (PY), we duplicate the fields used for CY and adjust them:

[PY] = DATEADD(‘year’, -1, {MAX(DATETRUNC(‘year’, [Order Date]))})

Similar to the IS PY, we create a field to flag entries within the same year as the current year:

[IS PY] = [PY] = DATETRUNC(‘year’, [Order Date])

This field helps us extract data for the previous year by subtracting one year from the maximum year in the dataset.

Step 4: Current Year (CY) Sales Calculation

Next, we calculate the sales for the current year, converting the boolean calculation to an INT type:

CY Sales = INT([IS CY]) & PY Sales = INT([IS PY])

This field ensures we only sum the sales for the current year.

Step 5: Difference Between Years

We calculate the difference between current year (CY) and previous year (PY) sales:

[YoY sales diff] = SUM([CY sales]) – SUM([PY sales])

Step 6: Calculating Year-on-Year (YoY) Percentage Difference

To present the data as a percentage, we create a calculated field for the YoY percentage difference:

[YoY % Difference] = [YoY sales diff] / SUM([PY sales])


We then format this field to display the percentage:

Right-click on the calculated field.
Navigate to Default Properties.
Select Number Format.
Choose Custom and enter “+0.0%;-0.0%.”

Step 7: Building the KPI Card

Now that we’ve prepared our data, it’s time to construct our KPI card. Add [CY sales] to the text shelf to display the current year’s sales. Next, add [YoY % Diff] to the text shelf to show the year-on-year percentage difference. Customize the text as needed to make the KPI card visually appealing.

And there you have it – a comprehensive KPI card that provides a clear snapshot of the current year’s sales and the year-on-year percentage difference. This powerful visualization aids in quick decision-making and provides valuable insights into business performance. By following these steps, you can create KPI cards tailored to your specific data and goals, enhancing your data-driven decision-making process.

The Data School
Author: The Data School