Forum Discussion
Rolling Last 3 Months Using Dates In Period
sfalk781
Your granularity of the FactInternetSales21 table is not at day level, there are multiple records per dates. You need to summarize. to get the correct averageMoving X Months Avg = CALCULATE( AVERAGEX( SUMMARIZE( FactInternetSales21, FactInternetSales21[Order Date], "Amount",SUM(FactInternetSales21[Sales Amount])), [Amount] ), DATESINPERIOD( FactInternetSales21[Order Date], LASTDATE(FactInternetSales21[Order Date]),-3,day) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
sfalk781
Your granularity of the FactInternetSales21 table is not at day level, there are multiple records per dates. You need to summarize. to get the correct average
Moving X Months Avg =
CALCULATE(
AVERAGEX(
SUMMARIZE(
FactInternetSales21,
FactInternetSales21[Order Date],
"Amount",SUM(FactInternetSales21[Sales Amount])),
[Amount]
),
DATESINPERIOD(
FactInternetSales21[Order Date],
LASTDATE(FactInternetSales21[Order Date]),-3,day)
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Fowmy thanks for your help. I applied the new measure and it works, but I can't really understand what you're saying. You mentioned that it's not at the "Day" level and there are "multiple" records per dates. In the image I provided, each row is a separate date (day) and I only see a single record for each date.
Can you help explain what I'm missing here?
sfalk781
What you showed me was the table visual, can you look at the source data in your table?
the formula basically summarizes the table by date and aggregates the value by date, I assigned the total value to a variable called "Amount" which eventually gets averaged.________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
