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.
