Rain average by month over a period of years, when there is no data in a month?

[ignaciol]

I need to make a chart with the rain average by month, for a period of years. The only Dimension is Month and the average should be for the period of years including those that didn't rain.

I can not use the avg function, because on several years no rain has fallen. Which functions should I use ??

Thank you !!

[johnw · Accepted solution]

So you have data like this?

Year, Month,   Rain
2008, January, 5
2009, January, 10
2011, January, 5

And you want to show that the average for these four years is 5, because the missing year 2010 should count as 0?  I think this:

sum(Rain)/(1+max(total Year)-min(total Year))

Edit: Alternatively, fill in the missing data in your data model.  This would allow for the much simpler avg(Rain), and should calculate faster.

[#3]

You might want to analyze the months when there is no rain. Might be a value add.

Best Regards,
Vishal

[IAMDV]

John - Simple yet clever approach. Thank you for sharing

Cheers - DV