Forum Discussion

AnalystDiogenes's avatar
AnalystDiogenes
Regular Visitor
2 years ago

Rolling Geometric Mean

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 w...
ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
AnalystDiogenes
Regular Visitor
2 years ago

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.