Calculating PYTD Sales KPI using DATEADD function 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 PYTD(Previous Year To Date) calculation?
PYTD stands for Previous Year To Date. It refers to the period starting from the beginning of the last year to the current date of that year. For example, if today’s date is March 6, 2024, then PYTD refers to the period from January 1, 2023, to March 6, 2023. Please note that PYTD is often used in financial and business contexts to compare the performance of a company or an investment over the same period in the previous year.
Building the KPI card for PYTD Sales
We will be making this KPI card:
So calculating PYTD 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 PYTD Sales:
PYTD_Sales = TOTALYTD(SUM(ecommerce_data[sales_per_order]), DATEADD(Calender[Date], -1, YEAR))
DATEADD function has three parts: Dates, NumberOfIntervals and Interval.
- For Dates we are giving the Calender[Date] column from our calendar table Calender.
- For NumberOfIntervals we are giving the value -1 because we want to go one year back from our current date.
- For Interval we are giving value YEAR as we want to subtract one year from the Calender[Date] to get dates from the previous year.
SUM(ecommerce_data[sales_per_order] is simply calculating the total sales with [sales_per_order] being the sales metric in the table ecommerce_data.
The TOTALYTD function is calculating the cumulative total of the first argument (the sum of sales per order), up to the last date of the year specified by the second argument (the date shifted back by one year).
So, in simple terms, PYTD_Sales is calculating the total sales from the start of the previous year to the same date of that year (as the current date this year).
3.Creating the KPI card:
Now get a card visual from Visualizations and put the PYTD_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 #2BAC1A) and Bold the Font.
then in Visual go to Category label, expand it choose a colour of your choice(I used Hex code #B3B3B3) and Bold the Font.
Next head to General, expand Effects and in Background choose a colour of your choice, I used Hex code #1A1A1A.
Finally we have our KPI card.
To create a dynamic card 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 create a dynamic KPI in Power BI 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
