How to calculate long-term average rainfall & dynamic moving averages in Power BI
This video is part of DiscoverEI's DAX for Enviro's series, and walks through how to calculate long-term averages and moving averages for daily rainfall data using DAX in Power BI. We explore the CALCULATE, ALL, ALLSELECTED and DATESBETWEEN DAX functions, and take it to the next level by creating a dynamic moving average analysis using What If parameters. Watch the video to learn about how to apply these DAX functions and play around with the Power BI report that we created in the video, embedded below.
The Scenario:
Let's imagine that I am an environmental engineer who is interested in analysing the trends in the average daily rainfall for a local rainfall gauge. While I know how to chart the daily rainfall, there is a lot of noise in the time-series, and I want to smooth out the noise by plotting the monthly moving average. I also want to know if the rainfall is above or below the long-term average daily rainfall.
DAX Magic:
For this will we use the queen of DAX functions - CALCULATE, which allows us to change the filter context of our calculations. We'll start simple by calculating the average daily rainfall:
Average Daily Rainfall = AVERAGE(ClimateData[Rain (mm)])
And then modify the date context to CALCULATE to calculate the long-term average rainfall:
Long-term average daily rainfall = CALCULATE([Average Daily Rainfall],ALL(Dates[Date]))
And finally, use DATESINPERIOD to calculate the average for the last month:
Monthly Moving Average =
CALCULATE (
[Average Daily Rainfall],
DATESINPERIOD (Dates[Date], LASTDATE (Dates[Date]), 1, MONTH)
)
For some extra bonus points, we'll make the moving average calculation dynamic by using a WHATIF parameter.
🎦 Check out the video 🎦
Recommended DAX Resources
As mentioned in the recording there are some AMAZING resources out there to help you with your DAX journey. Here is our pick of the bunch:
Power BI Docs: https://docs.microsoft.com/en-us/dax/dax-overview
DAX Guide: https://dax.guide
SQL BI: https://www.sqlbi.com/
RADACAD: https://radacad.com/new-video-course-dax-and-calculations-in-power-bi
Matt Allington: https://exceleratorbi.com.au/blog/
Chris Webb: https://blog.crossjoin.co.uk/
DAX Formatter: https://www.daxformatter.com/
DAX Studio: https://daxstudio.org/
DAX.Do: https://dax.do/