How to track annual groundwater usage in Power BI using DAX
This video is part of DiscoverEI's DAX for Enviro's series, and shows how to calculate your cumulative groundwater usage in Power BI using TOTALYTD, and create a DAX measure to compare against remaining groundwater licence entitlement volume. 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 a water resources manager of a small town where the primary water supply comes from 3 groundwater bores. I would like to visualise my annual groundwater usage to see how I'm tracking compared to my groundwater extraction license.
DAX Magic:
Introducing DAX time intelligence! Time intelligence functions make calculating trends over time a breeze in Power BI...We'll explore the TOTALYTD function to see how we can easily compute the cumulative total usage for a calendar year and also a financial year:
Cumulative groundwater usage = TOTALYTD(SUM('Groundwater Pumping'[Extraction Volume (ML)]),Dates[Date],Dates[Date],"30/06")
And then, we can calculate how much volume we remaining in our groundwater extraction license:
Remaining license volume = SUM('Bore Details'[Annual licenced extraction volume (ML)]) - [Cumulative groundwater usage]
Learn more about DAX Time Intelligence functions here.
🎦 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/