With Power BI, how to calculate the last year value in DAX? Simply use the two available DAX CALCULATE function combined with the PREVIOUSYEAR function. In financial dashboard and reports, use the previous year value for comparison and variance calculation with the current year. Indeed analyzing financial figures is often about time comparison and rolling forecasts.
How to calculate last year value in DAX for Power BI?
First option is to use directly the source column
The first option is to use directly the value from an existing column from the source table. This existing column is imported in a Tabular Model or in Power BI.
Sales Last Year := CALCULATE(SUM('ShopSales'[SalesAmount]), PREVIOUSYEAR('Time'[DateKey]))
Second option is to use another measure
The second option is to use a previously created measure and use it as a source, like in the second formula example.
Sales Last Year := CALCULATE([Sales], PREVIOUSYEAR('Time'[DateKey]))
To go further, the term DAX stands for Data Analysis Expression and it’s a Microsoft language used mainly in PowerPivot, Power BI reports and dashboards and also in Tabular Models.
Check out the detailed documentation of the two functions used in this article on Power BI website:
This article about the calculation of the last year value in DAX for Power BI, but it also applies to the creation of time measures in an Analysis Services tabular model.
Check this frequently asked questions about DAX measures
Per default, the last year sales are not calculated automatically by the software. However DAX for Power BI allows to create a measure using time functions to calculate previous year sales.
In Power BI there are many options to calculate new measures, including the ones with time functions. Use the base column mapped from a table or another measures.
DAX stands for Data Analysis Expressions and it’s used to create measures and calculations for Power BI and Power BI. MDX is used by Analysis Services cubes.
The DAX abbreviation stands for Data Analysis Expressions and it’s a Microsoft language used to manipulate data.