locked
Related table problem in Power Pivot RRS feed

  • Question

  • Hi,

    I have one table for sales data for different years for different companies. Then I created a calender kind of table with last_quarter as the previous quarter of the period. 

    Now I related both periods (in power pivot window) to get the below table

    Now I want to calculate QoQ growth in sales. for which i am trying to calculate sales for current quarter and previous quarter.

    For previous quarter I am looking for period = Last_quarter . Can anyone help me in writing the measure. Dont tell me to use calendar functions ,my fact table don't have date column also image these tables are really big 200k rows.

    Thanks

    Wednesday, August 14, 2013 9:29 PM

Answers

  • Hi Ronald,

    Formula should be Table1[Period]  = EARLIER(RELATED(Table2[Last_Quarter])) instead of EARLIER(Table1[Period])  = RELATED(Table2[Last_Quarter]).

    Hi NewBeeeeee HP,

    Actually, Ronald formulas should be work without using time intelligent functions. Basing on his solution, i make small sample to share on my skydrive as below

    https://skydrive.live.com/#!/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A!134&app=Excel

    Hope this help you clearly.

    Regards,

    • Proposed as answer by Elvis Long Friday, August 23, 2013 1:41 AM
    • Marked as answer by Elvis Long Monday, August 26, 2013 4:44 AM
    Friday, August 16, 2013 2:43 PM

All replies

  • Time intelligence functions (which require a date table) are the intended way to do these kinds of calculations. That said, one way to do what you're trying to do without dealing with dates is:

    1. Create a calculated column in your fact table for previous quarter sales:

    =CALCULATE(SUM([Sales]),
      FILTER(Table1, EARLIER(Table1[Period])  = RELATED(Table2[Last_Quarter]) &&
        EARLIER(Table1[Company]) = Table1[Company])) 

    2. Add a measure to do the QoQ calculation:

    DIVIDE(SUM(Table1[Sales]),SUM(Table1[PrevQuarterSales]))

    (DIVIDE is assuming you have the latest version of PowerPivot. Otherwise, you can use the division operator but you need to add checks for blanks in the denominator)

    There are probably other ways too.


    Thursday, August 15, 2013 8:20 PM
  • Time intelligence functions (which require a date table) are the intended way to do these kinds of calculations. That said, one way to do what you're trying to do without dealing with dates is:

    1. Create a calculated column in your fact table for previous quarter sales:

    =CALCULATE(SUM([Sales]),
      FILTER(Table1, EARLIER(Table1[Period])  = RELATED(Table2[Last_Quarter]) &&
        EARLIER(Table1[Company]) = Table1[Company])) 

    2. Add a measure to do the QoQ calculation:

    DIVIDE(SUM(Table1[Sales]),SUM(Table1[PrevQuarterSales]))

    (DIVIDE is assuming you have the latest version of PowerPivot. Otherwise, you can use the division operator but you need to add checks for blanks in the denominator)

    There are probably other ways too.


    Hi Ronald,

    I guess time intelligent functions wont work here because my fact table does not have a date field in it. Cant we deal with the problem in basic IF + Filter functions ?

    Thanks


    Friday, August 16, 2013 1:57 PM
  • Hi Ronald,

    Formula should be Table1[Period]  = EARLIER(RELATED(Table2[Last_Quarter])) instead of EARLIER(Table1[Period])  = RELATED(Table2[Last_Quarter]).

    Hi NewBeeeeee HP,

    Actually, Ronald formulas should be work without using time intelligent functions. Basing on his solution, i make small sample to share on my skydrive as below

    https://skydrive.live.com/#!/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A!134&app=Excel

    Hope this help you clearly.

    Regards,

    • Proposed as answer by Elvis Long Friday, August 23, 2013 1:41 AM
    • Marked as answer by Elvis Long Monday, August 26, 2013 4:44 AM
    Friday, August 16, 2013 2:43 PM