locked
Comparing YTD data to average of data in the last 6 months RRS feed

  • Question

  • Hi All, 

    I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following

    YTDProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(SUM('TrendData '[Projects]),DATESYTD(CalendarDate[FullDate])))

    PYProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(sum('TrendData '[Projects]),SAMEPERIODLASTYEAR(DATESYTD(CalendarDate[FullDate]))))

    YoYDifference:=[YTDProjects]-[PYProjects]

    YoYPercProjects:=IF([PYProjects]=0, BLANK(), [YoYDifference]/[PYProjects])

    where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table.

    But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period. Can some one please help with that?

    I tried the the DATEADD function instead but got no luck and data came out wrong!

    PrevProjects:=CALCULATE(SUM([Projects]),DATEADD(CalendarDate[FullDate],-1,QUARTER))

    For some reason, this also returns blank in my model:

    QTDProjects:=TOTALQTD(SUM('TrendData'[Projects]),CalendarDate[FullDate])

    Thank you in advance!

    Tuesday, June 9, 2015 10:01 PM

Answers

  • Hi AnEXCEL,

    According to your description, you need to calculate the average of data in the last 6 months, right?

    This is a common requirement which is called Moving Averages. In order to calculate a three month moving average, we need to be able to grasp a reference to the last 6 months and the current month – for each of the months listed on the table. The sample DAX expression looks like
    IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1,
        CALCULATE(
    SUM( FactResellerSales[SalesAmount] ) /  COUNTROWS(  VALUES ( DimDate[EnglishMonthName] ) )  ,
                DATESBETWEEN(
                    DimDate[FullDateAlternateKey],
                    FIRSTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], -6, MONTH)),
                    LASTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], 0, MONTH))
                ), ALL(DimDate)
        )
    )

    Please refer to the link below to see the details.
    https://javierguillen.wordpress.com/2011/09/13/calculating-moving-averages-in-powerpivot-dax/

    Regards,


    Charlie Liao
    TechNet Community Support


    • Edited by Charlie Liao Thursday, June 11, 2015 8:12 AM
    • Proposed as answer by Michael Amadi Thursday, June 11, 2015 8:56 AM
    • Marked as answer by Charlie Liao Wednesday, June 24, 2015 2:10 AM
    Thursday, June 11, 2015 8:08 AM

All replies

  • Hi AnEXCEL,

    According to your description, you need to calculate the average of data in the last 6 months, right?

    This is a common requirement which is called Moving Averages. In order to calculate a three month moving average, we need to be able to grasp a reference to the last 6 months and the current month – for each of the months listed on the table. The sample DAX expression looks like
    IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1,
        CALCULATE(
    SUM( FactResellerSales[SalesAmount] ) /  COUNTROWS(  VALUES ( DimDate[EnglishMonthName] ) )  ,
                DATESBETWEEN(
                    DimDate[FullDateAlternateKey],
                    FIRSTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], -6, MONTH)),
                    LASTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], 0, MONTH))
                ), ALL(DimDate)
        )
    )

    Please refer to the link below to see the details.
    https://javierguillen.wordpress.com/2011/09/13/calculating-moving-averages-in-powerpivot-dax/

    Regards,


    Charlie Liao
    TechNet Community Support


    • Edited by Charlie Liao Thursday, June 11, 2015 8:12 AM
    • Proposed as answer by Michael Amadi Thursday, June 11, 2015 8:56 AM
    • Marked as answer by Charlie Liao Wednesday, June 24, 2015 2:10 AM
    Thursday, June 11, 2015 8:08 AM
  • Hi Charlie,

    Thank you so much for your response. That worked perfectly well! I used it to calculate 3 month averages prior to a given month. I just have one follow up question. How do I tweak the formula, so it checks to see if there is no prior 3 month data, then don't calculate any averages?

    Right now with the given above formula it is calculating average for Jan 2014 when there is no prior monthly data for 2013. Any ideas why that might be happening?

    Row Labels SumofProj AVG3MProjects
    2014
    January 1515 2186.583333
    February 1260 1515
    March 1430 1387.5
    April 1383 1401.666667

    In the sample above, I should only see AVG3MProject number for April as it has 3 month previous data to compute. Not sure where Jan, Feb and March numbers are coming from.

    Any help is much appreciated!

    Friday, June 12, 2015 7:25 PM