DAX Power BI: Calculation of growth and increase using expressions for previous/next days, months, years
A special case of the PARALLELPERIOD function:
- NEXTYEAR, NEXTQUARTER, NEXTMONTH and NEXTDAY — create a column with dates of the previous year, quarter, month and day.
creating a column with the dates of the next year,
quarter, month and day - PREVIOUSYEAR, PREVIOUSQUARTER, PREVIOUSMONTH and
PREVIOUSDAY — creation of a column with the dates of
of the previous year, quarter, month and day
They cover the most popular requests to the PARALLELPERIOD function. That is, if we want to take the next month as a period, we do not need to write 3 parameters in PARALLELPERIOD, but just call the NEXTMONTH function and pass one argument to it — a column. Other functions from this group are used in a similar way.
Let’s look at an example.
This is how the code with the PARALLELPERIOD function will look like.
And this is how the code looks like using PREVIOUSMONTH. As you can see, the code has become smaller, it is easier to work with this code, but the result remains the same.
We can now calculate two useful real-world measures in analytics based on these date transfers.
Growth = (Current — Previous) / Previous
Growth = Current / Previous
Let’s calculate the corresponding measures in Power BI.
For division we will use the DIVIDE function to protect ourselves from division by zero.
This we have calculated the increase, now let’s calculate the growth.
The difference between growth and increment is that growth calculates always a positive value, i.e. growth always starts with zero. And in growth there can be negative values, because growth does not start from zero, but from -100 percent to 0. Zero means that we have reached the level of the previous month.
Don’t forget to subscribe to
and join our Power BI community
