Geometric mean at all grains

The goal of this example is to compute the geometric mean of a security at the day, month, and year grains.

• A table of security prices for the security by day exists in the model with a relationship to the date table

1. Create Last Price measures
   • The last price active in the filter context is used by each of the previous price measures (Day, Month, and Year)

Last Price = CALCULATE(VALUES(Daily Prices[Adj Close]),LASTNONBLANK('Date'[Date],[Max Daily Price]))
Previous Daily Price = CALCULATE([Last Price],
FILTER(ALL(Daily Prices),Daily Prices[IndexKey] = MAX(Daily Prices[Index Key]) - 1))
Previous Monthly Price = CALCULATE([Last Price],
FILTER(ALL('Date'),'Date'[Cal Year Month Index] = MAX('Date'[Cal Year Month Index]) -1)) 
Previous Year Price = CALCULATE([Last Price],
FILTER(ALL('Date'),'Date'[Calendar Year] = MAX('Date'[Calendar Year]) -1)) 

1. Create Percentage Change measures
   • These are the source values for the geometric mean calculations and thus are expressed as positive numbers

Daily Return% = DIVIDE([Last Price],[Previous Daily Price])
Monthly Return% = DIVIDE([Last Price],[Previous Monthly Price]) 
Yearly Return% = DIVIDE([Last Price],[Previous Year Price])

• As only inputs to the geometric mean calculation, these measures should be hidden from the Report view

1. Create daily, monthly, and yearly geometric mean % measures:

Daily Geometric Return = GEOMEANX(Daily Prices,[Daily Return%])-1
Monthly Geometric Return = GEOMEANX(VALUES('Date'[Calendar Yr-Mo]),[Monthly Return%])-1
Yearly Geometric Return = GEOMEANX(VALUES('Date'[Calendar Year]),[Yearly Return%])-1

• The GEOMEANX() function iterates over tables at the different grains and computes the Return% measure for each row
• The geometric mean (the product of values taken to the Nth root) is computed last against this list of values. 
• Visualize geometric mean

• Given the date table relationships the metrics would reflect date dimension filter selections