locked
Get Avg of quantities for last 3 months except current month RRS feed

  • Question

  • Hi all,

    I have tried various options but somehow not able to work this out. My requirement is simple. Let me explain my scenario

    I am querying a cube into PowerPivot (in excel) Model table. I use NON EMPTY in my query so that I do not get the balnk values. A sample representation of my data would be like below :

            
    Date SalesQuantity
    1/2/2015 4
    1/5/2015 3
    1/14/2015 5
    1/15/2015 1
    2/3/2015 2
    2/4/2015 4
    2/16/2015 5
    2/17/2015 1
    2/18/2015 6
    3/1/2015 5
    3/6/2015 3
    3/9/2015 2
    3/10/2015 1
    4/7/2015 7
    4/8/2015 8
    5/27/2015 9
    5/28/2015 7
    5/29/2015 4
                                      

    Now the result I want is as follows (Not the last column):

    Month Avg Quantity Calculation logic
    Jan-15 0.00 There is no month before Jan (0/ 3)
    Feb-15 4.33 Sum of Quantities in Jan   (13/ 3)
    Mar-15 10.33 Sum of Quantities in Jan & Feb (31/ 3)
    Apr-15 14.00 Sum of Quantities in Jan   & Feb & Mar (42/ 3)
    May-15 21.33 Sum of Quantities in Feb & Mar & Apr (64/   3)

    I tries using this formulae :

     
    =CALCULATE(SUM(Table[SalesQuantity]), DATESBETWEEN(Table[DATE],DATEFIRST(DATEADD(Table[DATE],-3,MONTH)),DATELAST(DATEADD(Table[DATE],-1,MONTH)))

    But that does not work. I tried breaking down the formulae to see if the DATEFIRST and DATELAST is giving me the correct one or not, and it looks like it is not. DATEFIRST and DATELAST only seems to work if there is a corresponding date match in the comparison month else it gives blank. For example if I am looking for date first for 5/28/205 it will give value if I have date value for 2/28/2015. Same is for Last date.

    How do I get this data ? Any pointers would help

    - Girija


    Please mark responses as answered if it helped you.. This helps others... - Girija Shankar Beuria


    Wednesday, February 3, 2016 12:52 PM

Answers

  • Do not use time intelligence functions with a (non-consecutive) date column in a fact table. Instead, create a separate Calendar table with one row for each day. The date filter you're looking for (assuming the result is reported per month as in your example) is

    DATESINPERIOD(Calendar[Date],MIN(Calendar[Date])-1,-3,month)

    Wednesday, February 3, 2016 5:32 PM
    Answerer