How to do YOY and QOQ calculation using DAX

[Anonymous]

can someone help me how to perform YOY%, QOQ% calculation using DAX.

YOY%= mar-17(net_sale) - mar-16(net_sale)/mar-16(net_sale)

QOQ%= mar-08(net-sale) - dec-07(net_sale)/dec-07(net_sale)

NOTE : All the fields are in string format

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Could you please mark the proper answer as solution?

Best Regards!
Dale

[v-jiascu-msft]

Hi @Anonymous,

The first step is changing the data type to a proper type. You can try it out in this file.

1. Create a date table.

Calendar = CALENDARAUTO()

2. Establish relationships with other two tables.

3. Create two measures.

YOY% =
VAR thisYear =
    SUM ( YOY[Net_sales] )
VAR lastYear =
    CALCULATE ( SUM ( YOY[Net_sales] ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
RETURN
    DIVIDE ( thisYear - lastYear, lastYear, 0 )

QOQ% =
VAR thisQ =
    SUM ( 'QOQ'[Net_sale] )
VAR lastQ =
    CALCULATE (
        SUM ( QOQ[Net_sale] ),
        DATESINPERIOD ( 'Calendar'[Date], MIN ( 'Calendar'[Date] ), -1, QUARTER )
    )
RETURN
    DIVIDE ( thisQ - lastQ, lastQ, 0 )

Best Regards,

Dale