3 easy steps to make a Rolling 12 Months KPI in Power BI
Power BI is a platform that allows you to connect to various data sources, transform and model your data, and create interactive dashboards and reports. Power BI can help you gain insights from your data and communicate them effectively.
What is a KPI?
A KPI, or a key performance indicator, is a measurable target that shows how well an individual or a business is performing in terms of meeting their goals. KPIs can be financial, such as net profit or revenue, or nonfinancial, such as customer satisfaction or retention. KPIs help organizations track their progress, identify their strengths and weaknesses, and make informed decisions to improve their outcomes.
What are Rolling 12 Months Calculations?
The rolling 12 months calculation, also known as Last Twelve Months (LTM) or trailing twelve months, is a method used to evaluate a company’s performance during the immediately preceding 12-month time period. This is not necessarily related to a fiscal year period, as the LTM references any 12-month period.
Building the KPI
We will be making this KPI:
To calculate Rolling 12 Months Sales we require a calendar table because there is a requirement of creating a calendar table whenever we are dealing with dates in Power BI.
1.DAX for calendar table creation:
Calender = CALENDAR(MIN(Orders[Order Date]), MAX(Orders[Order Date]))
where the arguments are the start and end dates in the main table which here is Orders and the MIN() and MAX() functions give us the min and max dates in Orders table which are nothing but our start and end dates.
Then this calendar table needs to be connected to the main table in data modelling section of Power BI. Just drag Date column in calendar table to the respective date column in main table. Refer this article for connecting the tables, it is very easy to do.
2.DAX for Rolling 12 Months Sales:
Rolling 12 Months Sales = CALCULATE(SUM(Orders[Sales]), DATESINPERIOD(Calender[Date], MAX(Calender[Date]), -12, MONTH))
Here we are using CALCULATE function which requires an expression which is SUM(Orders[Sales]) and filter which is DATESINPERIOD.
Now DATESINPERIOD requires 4 parameters — Dates, Start Date, NumberOfIntervals and Interval.
- For Dates we use our calendar table which here is Calender, we use its Date column giving in total Calender[Date].
- For Start Date we are using the MAX date in our calendar table as that is the latest date in our table which in general is used in Rolling 12 Months calculations , also if your calendar table has dates up to the current/present date of creation of DAX then use TODAY(), in my case the calendar table had dates up to December 2022 so I used MAX(Calender[Date]).
- For NumberOfIntervals use -12 because we are going 12 months back from our Start Date.
- For Interval use MONTH because we going back month by month.
3.Formatting the KPI:
Now get a card(new) visual from Visualizations and put the Rolling 12 Months Sales to it. In Visualization head to Format your visual in Shape select Rounded Rectangle and set Rounded Corners to 10%.
Next head to Callout values, expand Values choose a colour of your choice, Bold the Font and for Horizontal alignment select Center.
Next expand Label choose a colour of your liking, Bold the Font and in Position select Above Value.
Next go to Cards, turn on Glow and expand it, select a colour of your choice, set Offset to Inside and Position to Right.
Next go to Cards, turn on Accent bar and expand it, select a colour of your choice, set Width to 10 px and Position to Bottom.
And finally we have our KPI
To create a Concatenated KPI refer to this link.
To create a KPI with TopN refer to this link.
To create a KPI with an image refer to this link.
To read more stories like this you can follow me with this link.
References:
Don’t forget to subscribe to
and join our Power BI community
