Simplify Your Monthly Metrics in Power BI with JFMAMJJASOND Formatting
This article guides you through creating visually-appealing monthly metrics reports in Power BI by converting month names to abbreviations for a cleaner look.
Metrics vs. KPIs
What is a Metric?
A metric is a way to track how well your business is doing by measuring specific activities. It’s like a scorecard that shows you how close you are to your goals. You can measure almost anything, from downloads of a flyer to how many new leads turn into sales.
What is a KPI?
A KPI (Key Performance Indicator) is similar to a metric in that they both involve measurable values. However, KPI goes a step further. It connects those measurements to specific strategic goals of the business. Think of KPIs as benchmarks that tell you how well your metrics are tracking toward achieving those big-picture objectives. For instance, a KPI might be to increase market share by a certain percentage, and the metrics you track (like sales figures) would show you if you’re on target.
How do they relate to each other?
Imagine a pyramid. The base of the pyramid is made up of metrics, which are all the individual measurements you track. These metrics then feed into KPIs, which are like building blocks higher up on the pyramid. KPIs take those metrics and connect them to specific goals. Finally, at the top of the pyramid are the overall business objectives. So, metrics provide the foundation, KPIs give those measurements purpose, and both work together to help you achieve your big-picture goals.
Reference:
This article will show you how to easily convert your monthly metrics into a simpler, cleaner format using the JFMAMJJASOND abbreviation. We’ll break it down into 4 easy steps.
Step 1: Extract Month Number
Add a new column to your table and enter the following formula to extract the numerical month value from your date column. This month number will be useful later when sorting the month abbreviations chronologically:
MonthNumber = MONTH('Table1'[YourDateColumn])Step 2: Extract Month Names
Extract the full month names from your date column. These names will be converted to their corresponding JFMAMJJASOND abbreviations later. To do this, add another calculated column and use the following formula:
MonthNameLong = FORMAT('Table1'[YourDateColumn], "MMMM")Step 3: Convert Month Names to Abbreviations
To convert the full month names to JFMAMJJASOND abbreviations and avoid sorting issues, create a third calculated column. Here’s the trick: you’ll have to add spaces after the first letter for months with duplicate abbreviations (e.g., ‘January’ becomes ‘J’, ‘June’ becomes ‘J ‘ (single-space), ‘July’ becomes ‘J ‘ (double-space)). This will ensure proper sorting without error.
The following SWITCH statement accomplishes this:
MonthAbbr =
SWITCH(
Table1[MonthNameLong],
"January", "J",
"February", "F",
"March", "M",
"April", "A",
"May", "M ",
"June", "J ",
"July", "J ",
"August", "A ",
"September", "S",
"October", "O",
"November", "N",
"December", "D"
)Step 4: Sort Month Abbreviations by Month Number
For the final step, sort the ‘MonthAbbr’ column by the ‘MonthNumber’ column to ensure the months appear in the desired JFMAMJJASOND order. You can easily achieve this using the ‘Sort By Column’ feature in Power BI.
Now, simply replace your current month field with the newly created ‘MonthAbbr’ column, and your metrics will have a cleaner, more streamlined look. You’re done!
You can also apply this approach to your dimDate table. For more information on creating dimDate tables, you can refer to this article:
Thank you for reading!
I hope this case study provided valuable insights. If you have any questions, feel free to reach out.
For those interested in exploring more data storytelling and data visualization content, I consistently create such content on my Patreon page.
For your reference, the .pbix file can be downloaded here.
Don’t forget to subscribe to
and join our Power BI community
