How to Calculate Last Year Sales in Power BI
In Power BI, you can calculate last year’s sales by using a combination of DAX (Data Analysis Expressions) functions and measures.
Step 1: Create a Date Table
First, you need to have a date table in your Power BI model. This table should contain all the dates for the relevant time period.
For example, if you have sales data from January 1, 2019, to December 31, 2021, your date table should have records for each day within this range.
Step 2: Create a Measure for Sales
Next, you need to create a measure that calculates the sales amount. Let’s call this measure “SalesAmount”.
The formula for the “SalesAmount” measure would depend on how your sales data is structured.
Example 1: Sales Data with Date Column
If you have a date column in your sales data table, you can create the “SalesAmount” measure as follows:
SalesAmount = SUM(SalesData[Amount])
Example 2: Sales Data with Fact Table
If you have a fact table that contains sales data with a reference to the date table, you can create the “SalesAmount” measure using the RELATED function:
SalesAmount = SUMX(SalesFactTable, SalesFactTable[Amount])
Step 3: Calculate Last Year Sales
To calculate last year’s sales, you can use the SAMEPERIODLASTYEAR function in combination with the “SalesAmount” measure.
LastYearSales = CALCULATE([SalesAmount], SAMEPERIODLASTYEAR(DateTable[Date]))
Step 4: Display Last Year Sales in a Visual
Now that you have the “LastYearSales” measure, you can use it in any visual you want to display the last year’s sales.
For example, you can create a table visual and add the “LastYearSales” measure to the values section. The table will then show the last year’s sales for each date in the date table.