locked
28 Day Rolling Averages with Discrete Time Attributes RRS feed

  • Question

  • Hi All, I've been able to find plenty of info online in relation to calculating moving averages for a given value.

    I am having a specific problem however.

    So because I need to see the average on a daily basis, my forumla (below) works fine:

    However, my Calendar dimension (TIME) also has a discreet, non-continuous attribute called DateMonth (which looks like 1-Jan, 2-Jan etc.) which has no reference to the year.

    I was hoping this would allow me then to take my year attribute and compare the 28 Day Average for the 1st of Jan across multiple years.

    So you could have a line chart where the X axis is 1-Jan to 31-Dec (with no reference to the year) and the series category is by year, so a line for each year.

    But when I replace DATE with DateMonth in my pivot, the calc gives some strange numbers.

    Hope someone could help. Apologies if I haven't provided enough info :)


    Rolling28DayAvg :=
    CALCULATE (
        [Revenue],
        DATESINPERIOD (
            TIME[DATE],
            LASTDATE ( TIME[DATE] ),
            -28,
            DAY
        )
    )
        28

    Tuesday, July 14, 2015 8:37 AM

Answers

  • Hi,

    as soon as one member of the column "DateMonth" is selected, this selection adds to the current filter context.

    The Selection of one member of "DateMonth" and the calculation of the timeframe using DATESBETWEEN(...) do not match, due to the fact, that the selection of "DateMonth" has just one member in the timeframe, the last one.

    For this reason it is necesseary to expand the filtercontext using ALL('dim#time')

    Guess this is working

    Revenue_28DaysAvg_New := 

    if(HASONEVALUE('DIM#Time'[DateMonth]);
    CALCULATE([Sum of Revenue];all('DIM#Time');DATESINPERIOD('DIM#Time'[TIME_DATE];LASTDATE('DIM#Time'[TIME_DATE]);-28;DAY))/28;
    CALCULATE([Sum of Revenue];DATESINPERIOD('DIM#Time'[TIME_DATE];LASTDATE('DIM#Time'[TIME_DATE]);-28;DAY))/28
    )

    At least my chart looks somewhat similar to the chart you are looking for

    Of course there are missing values for 2015


    Tuesday, August 4, 2015 4:48 PM

All replies

  • Here is a view of what I need vs what I currently see.

    http://i.imgur.com/JEP9o51.png

    Tuesday, July 14, 2015 9:31 AM
  • Hi Data_Driver,

    According to your description, you are trying to create a moving average for your discrete time attributes, right?

    In your scenario, the calendar dimension is a noncontiguous attribute, so the moving average might be incorrect. So you can create a calendar table for your PowerPivot data model. And then create a moving average by using this new created calendar. Please refer to the link below to see the details about how to create a calendar table for PowerPivot data model.
    http://www.powerpivot-info.com/post/208-q-how-can-i-create-calendar-datetime-table-for-powerpivot-if-i-do-not-have-any-source-for-that

    Besides, here are some useful links about moving average on PowerPivot data model for you reference.
    https://sharepointmike.wordpress.com/2012/09/22/calculating-a-moving-average-in-powerpivot/
    http://www.powerpivotpro.com/2013/08/moving-averages-controlled-by-slicer/

    Regards, 


    Charlie Liao
    TechNet Community Support

    Thursday, July 16, 2015 3:01 AM
  • Hi Charlie,

    Thanks for the links, they look really helpful.

    So I already have a Calendar (TIME) dimension and linked to my main fact table on a dateid key. I can get my 28 Day moving average working perfectly when I view it with the date attribute from DIM.TIME. My date attribute is the normal, continuous attribute - 16/07/2015

    But when my measure is viewed with my other non-continuous (DayMonth: sample member: 16-July) attribute from the same TIME dimension, the numbers that are returned are completely incorrect.

    I will try to upload a sample of the model later if it helps?

    Thank you again for taking the time to respond. Really appreciate it.

    Thursday, July 16, 2015 3:00 PM
  • You will find below a link to a sample of my model with some dummy data.

    I think between the image above (linked only) and this sample workbook, it should be clear what I am trying to achieve. There must be a solution for this.

    https://drive.google.com/file/d/0B4j_4HhpjJeKQ1R2MF90T0lwdU0/view?usp=sharing

    Thursday, July 23, 2015 11:26 AM
  • So, having thought a bit more about this I have decided to create a new measure called Rolling28DayAvg_Revenue_PriorYear.

    This almost gets me here, but, if I filter on a single year, this measure returns null. So Again, Ideally what I need to see is:

    Rolling 28 Day Avg Rolling 28 Day Avg Last Year

    1/1/2015   xxxxxxxx   xxWhatever this value is on 1/1/2014

    1/2/2015  xxxxxxxxxxx  xxWhatever this value is on 1/2/2014

    My current formula is:

    CALCULATE (
        [Rolling28DayAvg_Revenue],
        SAMEPERIODLASTYEAR ( TIME[TIME_DATE] )

    Thursday, July 23, 2015 2:56 PM
  • Hi All,

    Disappointing that I haven't been able to solve this yet. Anyone willing to give it a second look? I hope from my code, image and sample model that my requirement is clear. Apologies if not.

    Thanks

     

    Monday, August 3, 2015 10:00 AM
  • Your sample workbook does not contain revenue
    for any year but 2015.

    Monday, August 3, 2015 7:26 PM
  • Hi Herbert,

    Apologies, I thought I fixed that. Should included 2014 data now. 

    Here is the new linK

    https://drive.google.com/file/d/0B4j_4HhpjJeKNlJPeS1JN3B4Zk0/view?usp=sharing

    Thanks,


    • Edited by data_driver Tuesday, August 4, 2015 8:42 AM
    Tuesday, August 4, 2015 8:41 AM
  • Hi,

    as soon as one member of the column "DateMonth" is selected, this selection adds to the current filter context.

    The Selection of one member of "DateMonth" and the calculation of the timeframe using DATESBETWEEN(...) do not match, due to the fact, that the selection of "DateMonth" has just one member in the timeframe, the last one.

    For this reason it is necesseary to expand the filtercontext using ALL('dim#time')

    Guess this is working

    Revenue_28DaysAvg_New := 

    if(HASONEVALUE('DIM#Time'[DateMonth]);
    CALCULATE([Sum of Revenue];all('DIM#Time');DATESINPERIOD('DIM#Time'[TIME_DATE];LASTDATE('DIM#Time'[TIME_DATE]);-28;DAY))/28;
    CALCULATE([Sum of Revenue];DATESINPERIOD('DIM#Time'[TIME_DATE];LASTDATE('DIM#Time'[TIME_DATE]);-28;DAY))/28
    )

    At least my chart looks somewhat similar to the chart you are looking for

    Of course there are missing values for 2015


    Tuesday, August 4, 2015 4:48 PM
  • The column "DateMonth" has an error value in the row for the year 1900.
    Other problems abound.
    Tuesday, August 4, 2015 7:20 PM
  • Hey Herbert, can you add a screenshot with the error, I'm not able to find the error using Excel 2013
    Tuesday, August 4, 2015 7:54 PM
  • Hi Tom,

    This is the solution I was hoping for. Let me try it and I will get back to you ASAP!

    Thursday, August 6, 2015 9:47 AM
  • Tom, this is working beautifully. I made a few butchered attempts at using combinations of ALL but your formula is working 100%.

    I really appreciate this, I definitely owe you a beer :)

    Thanks also to everyone for their input.

    Thursday, August 6, 2015 10:20 AM
  • You're welcome, a pleasure to help! Maybe someday I will claim that beer.

    I do not know. if you are aware of  www.SQLBI.com, Marco Russo und Alberto Ferrari have some great articles about Filter Context and Context Transition.

    Thursday, August 6, 2015 10:51 AM
  • I have seen it before yes but I need to invest some serious time in reading and understanding the stuff they publish.

    Thanks again Tom!

    Thursday, August 6, 2015 12:17 PM