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/
If you have an environmental DAX challenge that you’d like us to tackle in a future video then please write in the comments below or email the team at info@discoverei.com
Power BI Training Courses
If you’re interested in learning from our team, then we have a range of 2-day Power BI training courses available to fast-track your Power BI journey.
Click on the links below to learn more, and get in touch with the team today!
Purchase Training
Do you want to learn how to design engaging and intuitive Power BI reports, which communicate your key insights at a glance and tell your data story?
Our two-day Power BI training course is designed specifically for Power BI Professionals and Data Analysts, and provides our best practice tips, tricks and hacks to help you transform your data! We provide this course online, in-person group sessions, or customised in-house training for your team. Places are limited (max 8 participants per class) so secure your spot today!