Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to do YOY and QOQ calculation using DAX

 

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's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    Could you please mark the proper answer as solution?

     

    Best Regards!
    Dale

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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 )

    How_to_do_YOY_and_QOQ_calculation_using_DAX

     

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Could you please mark the proper answer as solution?

     

    Best Regards!
    Dale