none
Calculating YoY Growth Using powerpivot

    Question

  • Hi,

    I have created a power pivot just like one below. Now I want to find the YoY and QoQ growth with some measure functions. Basically (30.9-27.9)/27.9 for QoQ and (29-27.9)/27.9 for YoY growth.

    Period     Sum of Units

    2009Q3     27.9m

    2009Q4     30.9m

    2010Q1      27m

    2010Q2      24m

    2010Q3       29m

    Any Suggestion on how to do it?

    Tuesday, August 06, 2013 7:33 PM

Answers

All replies

  • I assume you have a date table related to your fact data.

    [YoY]:=([Sum of Units]-[Sum of Units](PreviousYear(DateTable[DateKey])))[Sum of Units](PreviousYear(DateTable[DateKey]))

    [QoQ]:=([Sum of Units]-[Sum of Units](PreviousQuarter(DateTable[DateKey])))[Sum of Units](PreviousQuarter(DateTable[DateKey]))

    Tuesday, August 06, 2013 11:48 PM
  • Sry I am new to power-pivot. Can u please explain wht is Date table, Datekey and PreviousYear functions are?
    Wednesday, August 07, 2013 4:08 AM
  • I assume you have a date table related to your fact data.

    [YoY]:=([Sum of Units]-[Sum of Units](PreviousYear(DateTable[DateKey])))[Sum of Units](PreviousYear(DateTable[DateKey]))

    [QoQ]:=([Sum of Units]-[Sum of Units](PreviousQuarter(DateTable[DateKey])))[Sum of Units](PreviousQuarter(DateTable[DateKey]))

    Hi Karen,

    The problem is my fact data has only period column in it, no date column. I have created a calendar file but should I create relation between period column in both? 

    Thanks

    Thursday, August 08, 2013 2:55 AM
  • Sry I am new to power-pivot. Can u please explain wht is Date table, Datekey and PreviousYear functions are?

    Hi NewBeeeeeee,

    Please refer to the following article:
    PREVIOUSYEAR Function: http://technet.microsoft.com/en-us/library/ee634770.aspx

    Here is a article for your reference, please see:

    Get the YTD of same period last year using DAX: http://www.powerpivotblog.nl/get-the-ytd-of-same-period-last-year-using-dax

    Best Regards,


    Elvis Long
    TechNet Community Support

    Thursday, August 08, 2013 3:44 AM