YOY calculation for vertical data defined data using dax

[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 with help provided from this forum. 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:

Data

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

Output

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%

Date Table content where relationships are already made and cant be edited:

Thanks,

Rohit

[Anonymous · Accepted solution]

HI @rohit403 ,

Your period fields seems not stored valid date values. You can try to use following measure formula if it works for your requirement:

YOY =
VAR currPeriod =
    MAX ( Table[Period] )
VAR curr =
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER ( ALLSELECTED ( Table ), Table[Period] = currPeriod ),
        VALUES ( Table[Customer] ),
        VALUES ( Table[KPI] )
    )
VAR prev =
    curr
        = CALCULATE (
            SUM ( Table[Value] ),
            FILTER (
                ALLSELECTED ( Table ),
                Table[Period]
                    = LEFT ( currPeriod, 3 )
                        & VALUE ( RIGHT ( currPeriod, 4 ) ) - 1
            ),
            VALUES ( Table[Customer] ),
            VALUES ( Table[KPI] )
        )
RETURN
    IF ( prev <> BLANK (), DIVIDE ( curr - prev, prev, 0 ) )

If above not help, please share a pbix file with some sample data for test.

Regards,

Xiaoxin Sheng