3 easy steps to make a Current Week Sales 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.
Building the KPI
We will be making this KPI:
So to calculate current week sales we require Week Number and Year from the dates column of our data which 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:
Calendar = CALENDAR(MIN(car_data[Date]), MAX(car_data[Date]))
where the arguments are the start and end dates in the main table which here is car_data and the MIN() and MAX() functions give us the min and max dates in car_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.
Also we need to extract Year and Week from the calendar table for which head to table view select the calendar table which here is Calendar, add a New Column from the panes in the top and use these two DAXs:
Year = YEAR(Calender[Date])
Week = WEEKNUM(‘Calendar’[Date])
2.DAX for Current Week Sales:
Cur Week Sales = CALCULATE(SUM(car_data[Price ($)]), Calendar[Year] = YEAR(TODAY()), Calendar[Week]=WEEKNUM(TODAY()))
Here we are using CALCULATE function which requires an Expression and Filters.
- For the Expression we are using SUM(car_data[Price ($)]) which is the quantity we are calculating in this case.
- For the Filters we will be using 2 filters — Calendar[Year] = YEAR(TODAY()) and Calendar[Week]=WEEKNUM(TODAY()). The WEEKNUM(TODAY()) will simply find the week number for the current date using TODAY() but it doesn’t know which year to look into especially when you have data from multiple years so we are specifying here that we want data filtered for the current year using YEAR(TODAY()) which extracts the YEAR of current date.
So now the CALCULATE function filters dates which are in the current year and in the current week and then finds the sum of the quantity car_data[Price ($)] which is simply sum of sales.
3.Creating the KPI:
Now get a card visual from Visualizations and put the Cur Week 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.
You can modify the above DAX to calculate the Previous Week Sales, give it a try its easy.
To create a ranked table refer to this link.
To create a KPI with a trend indicator refer to this link.
To create a KPI with a unit refer to this link.
To create a Dynamic Title refer to this link.
To create a Rolling 12 Months KPI 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
