Rolling Geometric Mean

[AnalystDiogenes]

I have some (sparse/messy) data on 'headline rents' in a time series that doesn't have very consistent amounts of data each month. So I want a geometric mean (less affected by extreme values) and I want it rolling over different time periods (3 months, 1 year, 10 years).

But what I want is that I get the rolling window and then do the geometric mean. The idea is to avoid taking an average of averages made up of different amounts of data.

I'm having no luck.

Strategy 1: GEOMEAN

3 Month Rolling Geometric Average = CALCULATE(
    GEOMEAN(Rent_Data[Headline_Rent]), 
    DATESINPERIOD(Rent_Data[EFFECTIVE_DATE], LASTDATE(Rent_Data[EFFECTIVE_DATE]), -3, MONTH)
    )

When I plot this as a time series, and drill down to year quarter view, it collapses into the geometric mean (without rolling), and generally doesn't interact with drill down as I would expect.

Strategy 2: GEOMEANX

Roll_Geommean=CALCULATE(
       GEOMEANX(
           SUMMARIZE(
               'Dates',
               'Dates'[Year],
               'Dates'[Qtr]
           ),

        'Rent_Data'[Headline_Rent]
        ),
        DATESINPERIOD(
            'Dates'[Date Key],
            LASTDATE(
               'Dates'[Date Key]
                ),
             -12,
            MONTH
            )
      )

This strategy doesn't work because [Headline_Rent] isn't yet aggregated  by date; there are some dates with multiple values and some with none

Strategy 3: Break the geometric mean into easy steps

1. I calculate the LOG('Rent_Data'[Headline_Rent]) and do a YTD total for that as a quick measure
2. I calculate the COUNT of rents YTD
3. I calculate the LOG of step 2.
4. I calculate EXP(step 1/step3) to get a Year to Date geometric mean

   If I can get this to work I can try other time spans.

   But I get the error:

   

   There must be a way of doing what I want to do, but I can't work out what it is.

   And help much appreciated.

[ValtteriN]

Hi,

generally speaking I recommend using a calendar table when dealing with time intelligence calculations. Even better use a calculation group. Here are examples:

CALCULATE( selectedmeasure(), DATESINPERIOD('table (21)'[date], LASTDATE('table (21)'[date]), -3, MONTH) )

CALCULATE( selectedmeasure(), DATESINPERIOD('calendar'[date], LASTDATE('calendar'[date]), -3, MONTH) )

 
The former calculation group uses calendar and the latter doesn't. Now let's see how this affects the end results.

Measure:

GEODEMO = GEOMEAN('Table (21)'[Value])

Calc groups applied:

[AnalystDiogenes]

I have a date heirarchy in 'Dates' that is generated from =CALENDARAUTO() . It didn't seem to make any difference, but I didn't use it in the example of Strategy 1 in case moving between different tables was causing an issue.

3 Month Rolling Geometric Average = CALCULATE(

     GEOMEAN(Rent_Data[Headline_Rent]), 

     DATESINPERIOD(Dates[Date Key], LASTDATE(Dates[Date Key]), -3, MONTH))

This doesn't make any difference for Strategy 1, and I've already used it in Strategy 2.
But I'm not sure I understand the difference between:

CALCULATE( selectedmeasure(), DATESINPERIOD('table (21)'[date], LASTDATE('table (21)'[date]), -3, MONTH) )

and

CALCULATE( selectedmeasure(), DATESINPERIOD('calendar'[date], LASTDATE('calendar'[date]), -3, MONTH) )

It looks like the name of the table is all that's different, and in your note you say one is a 'calendar' and the other isn't. I'm not sure is my 'Dates' table (=CALENDARAUTO()) counts as a calendar.