How to create vs Previous Period Measures on Power BI.

Creating a vs Previous period measure is very simple, we will make use of the DATEADD DAX function to create this measure.
Assuming that we already have our Sales measure, we will create a previous year sales measure:

PY Sales = CALCULATE([Sales], DATEADD(Dates[Date], -1, YEAR)


As you can see above, we are using DATEADD function to get the values from same period last year, if we want the values of previous month, we can replace “YEAR” with “MONTH” and we will get the previous month values. Note that when we use this function, it takes the value of the entire month, not until current day, for that we have another measure which you can find below.

Now, we will create vs Previous Period measure.

vs PY = [Sales] – [PY Sales]

 Vs PY % = DIVIDE([Vs PY], [PY Sales])

How to create a measure that accurately calculates the % against previous year.

First, we will go to our date table and create 2 calculated columns, one for current month and the other for current year. The measures are as follows:

CY =

IF(YEAR(Dates[Date])=YEAR(TODAY()),

    1)
——————————————————

CM =

IF(

    AND(

        YEAR(Dates[Date])=YEAR(TODAY()),

        MONTH(Dates[Date])=MONTH(TODAY())),

    1)

 

——————————————————————————-

Next, we will create a measure to use for if condition in our final measure.

C_M = AVERAGE(Dates[CM])

 

Now, we will create our final measure, which will accurately calculate the previous year sales starting from previous year till today’s day but in previous year.

PY Sales =

IF([C_M] =1,

    CALCULATE([Sales],

                       DATEADD(DATESBETWEEN(Dates[Date],MIN(Dates[Date]),TODAY()),-1,YEAR)),

 

    CALCULATE([Sales],DATEADD(‘Dates'[Date],-1,YEAR)))

 

 

Scroll to Top