{"id":8666,"date":"2023-06-19T06:33:00","date_gmt":"2023-06-19T04:33:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=8666"},"modified":"2023-10-19T15:57:22","modified_gmt":"2023-10-19T13:57:22","slug":"last-year-value-dax-power-bi","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/power-bi\/last-year-value-dax-power-bi\/","title":{"rendered":"How to calculate last year value in Power BI with DAX formulas?"},"content":{"rendered":"\n

Power BI tutorial on how to use the SAMEPERIODLASTYEAR function in a DAX formula to calculate the last year value.<\/em><\/strong><\/h4>\n\n\n\n

To calculate the previous year value in a Power BI formula, simply use the two available DAX CALCULATE function combined with the PREVIOUS YEAR function. In financial dashboard and reports, use the previous year value for comparison and variance calculation with the current year. Indeed analysing financial figures is often about time comparison and rolling forecasts. Financial forecast uses rolling months, quarters or years.<\/p>\n\n\n\n

Use the SAMEPERIODLASTYEAR time function in Power BI<\/h2>\n\n\n\n

The SAMEPERIODLASTYEAR function in DAX is an advanced time-intelligence function tailored to make comparative analyses across identical periods in consecutive years. Unlike the Power BI PREVIOUSYEAR<\/code> function, which only shifts the date context back by a year, SAMEPERIODLASTYEAR maintains the structure of your specified time period<\/strong>, whether it’s a quarter, month, or even a custom time span.<\/p>\n\n\n\n

This particular feature is beneficial to identify seasonal patterns or irregularities in the data<\/strong>. Importantly, this function works most effectively when you have a well designed date table in your Power BI model and that this table is marked as a date table<\/strong>. Doing so ensures that your date hierarchies are accurately understood by DAX, allowing SAMEPERIODLASTYEAR to execute seamless transitions in the time context.<\/p>\n\n\n\n

For instance, with a measure names sales, representing the current year:<\/p>\n\n\n\n

Sales := SUM('Sales'[Amount])<\/pre>\n\n\n\n
It is possible create a new DAX measure for the previous year sales, called Sales LY, in order to get last year’s sales for the same date range. This feature enables nuanced analysis and lends greater fidelity to your business intelligence insights.<\/p>\n\n\n\n
Sales LY := CALCULATE([Sales], SAMEPERIODLASTYEAR( 'time'[date_key]))<\/pre>\n\n\n\n
This formula would filter the sales data to include only the revenue from the previous calendar year, allowing for nuanced and strategic business planning.<\/p>\n\n\n\n
Calculate last year value in Power BI using one DAX formula <\/h2>\n\n\n\n
The simplest option is to use directly the value from an existing column from the source table. The developer or business user imports the existing column in a Tabular Model or Power BI. Please note that it is necessary to use the REMOVEFILTERS function to display the results.<\/p>\n\n\n\n
Sales Last Year = \nCALCULATE (\n    SUM ( sales[sales] ),\n    SAMEPERIODLASTYEAR ( 'time'[date_key] ),\n    REMOVEFILTERS ( sales )\n)<\/pre>\n\n\n
\n
\"Calculate
Calculate last year value in a Power BI report<\/em><\/figcaption><\/figure><\/div>\n\n\n
To go further, the term DAX stands for Data Analysis Expression<\/a><\/strong> and it’s a Microsoft language. For example, PowerPivot, Power BI reports and dashboards and also Tabular models use the DAX language. Check out the detailed documentation of the two functions from this article on the official Power BI website:<\/p>\n\n\n\n