Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## Rolling Total and Moving 12 Month Average

Hello everyone, I am trying to create 2 matrixes. For the first matrix, I want to look at the total sum for each user by month/year divided by the number of stores they are responsible for, giving me the avg monthly total per store. (total/number of stores)

For the second matrix, I want to look at a rolling 12-month moving average for a user divided by the number of stores they are responsible for, giving me the moving 12-month avg per store. (rolling 12-month avg/number of stores)

So, it feels like a simple solution but I am having an issue with calculating a rolling sum. If I can calculate the rolling sum, dividing by the number of stores will give me a user's monthly spend by store. From there I believe to get the moving 12-month average, I could divide by 12 or use the datesinperiod expression.

Here is the rolling sum formula with errors:

Total Running Total =
DIVIDE (CALCULATE(
SUM ('All Amazon and Bento'[Amount]),
FILTER (
'Date_Value',Date_Value[DateValue] <= MAX( 'All Amazon and Bento'[Order Date] )
)
),
[Number of Stores Measure]
)

Here is the Total per store measure:
Total per Store Measure =
CALCULATE (
DIVIDE (
'All Amazon and Bento'[Amazon Sum] + 'All Amazon and Bento'[Bento Sum],
[Number of Stores Measure]
)
)
I should be seeing the sum grow each month, from there I can then add the datesinperiod to limit to transfer it to a moving 12-month sum. Here are the matrixes:

I'm not sure why I am not seeing a rolling sum on the first matrix when the formulas are clearly different.

Thank you in advance!

1 ACCEPTED SOLUTION
Community Support

Hi @james_pease,

It seems like a common issue when measure expression calculates with multiple aggregates, you can take a look at the following blog to know how to handle this scenario:

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
Helper III

I think I might have a solution, I am double-checking the data manually to see if the numbers add up:

Moving Average per Store Measure =
CALCULATE (
DIVIDE (
AVERAGEX ( 'All Amazon and Bento', SUM ( 'All Amazon and Bento'[Amount] ) ),
[Number of Stores Measure]
),
DATESINPERIOD (
Date_Value[DateValue],
LASTDATE ( Date_Value[DateValue] ),
-12,
MONTH
)
)
Community Support

Hi @james_pease,

It seems like a common issue when measure expression calculates with multiple aggregates, you can take a look at the following blog to know how to handle this scenario:

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper III

Thank you, I was hoping to avoid using Summarize as I will lose filter context but it appears this would be the best solution. Thank you so much!

## Helpful resources

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors