How to create a PMTD 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 PMTD(Previous Month To Date) calculation?
PMTD stands for Previous Month to Date. It refers to the period starting from the beginning of the last calendar month and ending at the current day of the last month. In the context of data analysis, PMTD calculations are often used to compare data from the same period in the previous month. For example, if today is March 15, the PMTD would refer to the data from February 1 to February 15. This allows for a more accurate comparison of data trends over time.
Building the KPI card for PMTD Sales
We will be making this KPI card:
So calculating PMTD Sales requires the need for 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(ecommerce_data[order_date]), MAX(ecommerce_data[order_date]))
where the arguments are the start and end dates in the main table which here is ecommerce_data and the MIN() and MAX() functions give us the min and max dates in ecommerce_data 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 PMTD Sales:
PMTD Sales = TOTALMTD(SUM(ecommerce_data[sales_per_order]), DATEADD(Calender[Date], -1,MONTH))
TOTALMTD requires 2 parameters : Expression and Dates.
For Expression we are using SUM(ecommerce_data[sales_per_order]) in which we are calculating sum of [sales_per_order] which is our sales metric from the table ecommerce_data and for Dates we are using the DATEADD function that shifts dates in the Calender[Date] column backwards by one month. This is used to get the corresponding date range in the previous month.
So, in summary, this DAX formula calculates the total sales per order for the previous month up to the current day of the month. To learn more about DATEADD function refer to this link. It is a short and an easy article to understand.
3.Creating the KPI card:
Now get a card visual from Visualizations and put the PMTD Sales to it.
In Visualizations head to Format your visual then in Visual go to Callout value, expand it choose a colour of your choice(I used Hex code #586C55) and Bold the Font.
then in Visual go to Category label, expand it choose a colour of your choice(I used Hex code #605E5C) and Bold the Font.
Next head to General, expand Effects and in Background choose a colour of your choice, I used Hex code #F4F9F3.
Finally we have our KPI card.
To create MTD Sales KPI refer to this link.
To create PYTD Sales KPI refer to this link.
To create a matrix with sales percentages remaining unchanged refer to this link.
To create a multi conditioned SWITCH function refer to this link.
To go to Part 1 of the Professional KPI series 2 refer to this link.
To read more stories like this you can follow me with this link.
Don’t forget to subscribe to
and join our Power BI community
