Calculate Running Average Using DAX in Power BI

Learn how to compute running averages using DAX in Power BI. This guide provides a step-by-step breakdown to help you get accurate results, ensuring that your reports reflect true sales performance.
---
This video is based on the question https://stackoverflow.com/q/67429190/ asked by the user 'Chicken Sandwich No Pickles' ( https://stackoverflow.com/u/5560898/ ) and on the answer https://stackoverflow.com/a/67429587/ provided by the user 'AntrikshSharma' ( https://stackoverflow.com/u/14904968/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Calculate Running Average Using DAX in Power BI

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Calculate Running Average Using DAX in Power BI

When working with sales data in Power BI, one common requirement is to compute the running average of sales over a given period. This is especially useful for understanding performance trends and making informed decisions. However, achieving accurate running averages requires the right approach in the DAX (Data Analysis Expressions) code. In this guide, we’ll walk through how to correctly calculate the running average while ensuring that relevant dates are included or excluded according to your dataset.

The Problem

Imagine you have a table of sales data like the following:

datesales_amount04/01/2021100.0004/02/2021300.0004/05/2021500.00You want to calculate the running average, taking into account days when no sales were made (like 04/03 and 04/04). Your expected output should look like this:

datesales_amountrunning_average04/01/2021100.00100.0004/02/2021300.00200.0004/05/2021500.00300.00Unfortunately, the initial DAX formula you are using isn’t producing the correct running average, particularly due to how dates without sales are handled. Let's dive into a solution that will give you the accurate results you’re looking for.

Understanding the DAX Solution

Your current DAX formula needs some adjustments. Here’s what to focus on:

Key Variables in the DAX Code

LastVisibleDate: This variable captures the maximum date that is visible in the context of the report.

[[See Video to Reveal this Text or Code Snippet]]

FirstVisibleDate: This variable captures the minimum date that is currently in view.

[[See Video to Reveal this Text or Code Snippet]]

LastDateWithSales: This variable retrieves the maximum date when sales data is available, but you must ensure that filters from the report context do not affect this calculation.

[[See Video to Reveal this Text or Code Snippet]]

Correcting Your DAX Formula

Adjusting the CALCULATE Function: Replace the filter clause in the CALCULATE function to correctly reference the last visible date to ensure accurate filtering.

[[See Video to Reveal this Text or Code Snippet]]

Complete Revised DAX Code:

Here’s how your updated DAX formula should look:

[[See Video to Reveal this Text or Code Snippet]]

Best Practices

Use Date Tables: For optimal performance and more consistent behavior, always use a dedicated date table in your model. This allows the DAX engine to optimize calculations and apply appropriate filters automatically.

Testing Your Results: Once you’ve implemented the new DAX formula, validate the output against known values to ensure that it reflects the accurate running averages for the time periods without sales.

Conclusion

By applying the right DAX calculations and ensuring that your filter context is accurately defined, you can achieve a correct running average in Power BI. This not only enhances the accuracy of your reports but also aids in making data-driven decisions. Following the steps outlined above will help you avoid common pitfalls and improve the way you analyze and report on your sales data.

Feel free to reach out if you have any questions or need further assistance with DAX in Power BI!

Видео Calculate Running Average Using DAX in Power BI канала vlogize