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(n...
  • 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

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