With Power BI, how to calculate the last year value in DAX? 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 analyzing financial figures is often about time comparison and rolling forecasts. Financial forecast uses rolling months, quarters or years.
How to calculate last year value in DAX for Power BI?
The first option calculate last year total is to use directly the source column in DAX
The first 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.
Sales Last Year := CALCULATE(SUM('ShopSales'[SalesAmount]), PREVIOUSYEAR('Time'[DateKey]))
Second option to get last year value with another measure from the Power BI model
The second option is to use another DAX measure and use it as a source, like in the below DAX 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. 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:
- The official documentation for the DAX CALCULATE function.
- And the official documentation for the DAX PREVIOUSYEAR function.
This article is about the calculation of 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 and Power BI
Per default, the last year sales are not calculated automatically by the software. However, DAX for Power BI allows creating a measure using time functions to calculate the 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 other measures.
DAX stands for Data Analysis Expressions and it’s used to create measures and calculations for Power BI and Power BI. For example, Analysis Services cubes contain MDX code.
The DAX abbreviation stands for Data Analysis Expressions and it’s a Microsoft language that allows users to manipulate data.
