Moving average calculation in Databricks AI/BI dashboard

[meljung]

So, I can't figure out how to do moving average as custom calculation in Databricks dashboard. I'm applying many different filters and the denominator of the metric has to change dynamically based on the chosen filters. So, in this case using `Custom Calculation` worked fine and example calculation of the metric is shown below. But now I'm facing a problem where I need to take a moving average of this dynamically calculated metric. I can't do the calculation in the query because the calculation wouldn't be correct when filtered. I also can't use parameters as filters because some business use cases limit the parameters. I'd appreciate if you have any suggestions.

[mark_ott · Accepted solution]

Currently, Databricks dashboards do not support applying a moving average “custom calculation” on top of another custom metric that itself is dynamic with respect to the filters.

Workarounds

• Segmented SQL Datasets: Pre-compute the filtered sets (as much as possible) in the SQL layer, then apply the moving average calculation there. Pass any possible filter through widgets or parameterize the dataset if business constraints allow.

• Export and Post-Process: For highly dynamic cases that cannot be handled in the dashboard or SQL alone, export the filtered data (e.g., for a given set of filters), calculate the moving average externally (such as in a Databricks notebook), and visualize that result in the dashboard.

• Product Wishlist: If true moving average on custom calculations in the dashboard is critical, consider submitting a feature request via official support or community forums. This scenario—window functions on top of dynamic aggregates in dashboards—is a known product limitation