yoy calculation in DAX with date table and vertical format

[rohit403]

Hi All,

I need your advise and help in calculating YOY (year on year) percentage change. The data as well as the result required is in the below format. I have a date table too, whose screenshot I have also provided. I must mention that I was able to do this by inserting a column in the Power BI table view. However, as my project is on a server, I cant edit the table and can just create a new measure. However I am struggling with the DAX formula for the same. Please help:

Customer	KPI	unit	Period	Value
Verizon	Total revenue	USD million	Q1/2016	29029.95849
Verizon	Total revenue	USD million	Q1/2017	27959.3623
Verizon	Capex	USD million	Q1/2016	10029.95849
Verizon	Capex	USD million	Q1/2017	8959.3623
Bharti	Total revenue	USD million	Q1/2016	34567
Bharti	Total revenue	USD million	Q1/2017	33000
Bharti	Capex	USD million	Q1/2016	15222
Bharti	Capex	USD million	Q1/2017	13655

Customer	KPI	unit	Period	Value		yoy%
Verizon	Total revenue	USD million	Q1/2016	29029.95849
Verizon	Total revenue	USD million	Q1/2017	27959.3623		-3.7%
Verizon	Capex	USD million	Q1/2016	10029.95849
Verizon	Capex	USD million	Q1/2017	8959.3623		-10.7%
Bharti	Total revenue	USD million	Q1/2016	34567
Bharti	Total revenue	USD million	Q1/2017	33000		-4.5%
Bharti	Capex	USD million	Q1/2016	15222
Bharti	Capex	USD million	Q1/2017	13655		-10.3%

Thanks

[v-jiascu-msft]

Hi Rohit,

How about a calculated column? Please refer to the formula and snapshot below.

Column =
VAR customer = [Customer]
VAR kpi_ = [KPI]
VAR currentValue = [Value]
VAR currentIndex = [Index]
VAR lastPeriodValue =
    CALCULATE (
        SUM ( Table1[Value] ),
        FILTER (
            Table1,
            Table1[Index] = currentIndex - 1
                && Table1[Customer] = customer
                && Table1[KPI] = kpi_
        )
    )
RETURN
    DIVIDE ( currentValue - lastPeriodValue, lastPeriodValue, BLANK () )

Best Regards,

[rohit403]

Hi v-jiascu-msft ,

Thanks for your quick response.

However I have a challenge here, I am doing these calculations on an online database where I can't edit the tables. I have to do the calculations in the report only using measures.

So can we do that without modifying the tables.

Once again thanks so much for your help and time.

Thanks,

Rohit

[v-jiascu-msft · Accepted solution]

Hi Rohit,

I created a new version of the measure that works more generally. Please give it a try.

Measure 2 =
VAR lastPeriod =
    CALCULATE (
        MAX ( test01[Period] ),
        FILTER (
            ALL ( test01[Period] ),
            DATEVALUE ( REPLACE ( test01[Period], 1, 1, 0 ) )
                < DATEVALUE ( REPLACE ( MIN ( test01[Period] ), 1, 1, 0 ) )
        )
    )
VAR lastValue =
    CALCULATE (
        MAX ( test01[Value] ),
        FILTER ( ALL ( test01[Period] ), test01[Period] = lastPeriod )
    )
RETURN
    DIVIDE ( MIN ( test01[Value] ) - lastValue, lastValue, BLANK () )

Best Regards,

[rohit403]

Hi v-jiascu-msft ,

Thanks for the solution. It works perfectly.

Just wanted to understand how the Dax syntax will change if I have a date table for this syntax to work. The content of the date table is given below:

I may be asking too many questions. However, I am working on a transition project, where I need to convert all my projects to Power BI and I am trying to do it as well as learn things simultaneously.

Look forward to your response.

Thanks,

Rohit

[LivioLanzo]

Hi@rohit403 

are you not able to import the data into the Power BI data model and shape it using Power Query?

[rohit403]

Hi LivioLanzo,

I cant export or edit the data on the server, can just add measures and visualisations.

Thanks

[LivioLanzo]

Hi @rohit403 

I have uploaded a file here with the calculation for you.

It is with Excel, but in Power BI it works the same: https://1drv.ms/x/s!AiiWkkwHZChHj12jSeGdaQ9oAVmQ

[rohit403]

Hi LivioLanzo,

First of all thanks for your prompt response.

However the challenge that I am facing is I have a huge data set in the same format (vertical/long). I am using this data set for creating charts for a report (which need the data to be in vertical format). Now when I have to calculate YoY % change, I have to use the same dataset.

So preferably, I am looking for a DAX formula which from the same dataset (in vertical format) can calculate YoY change.

Thanks,

Rohit