Forum Discussion
Rolling Last 3 Months Using Dates In Period
Hey everyone,
I'm using Dates in Period to look back to get the average of 3 days sales. The measure looks correct to me but the numbers aren't adding up to the right amount. In fact, if I change Average to Sum, it works perfect. Can anyone tell me, with the images provided, why the averages are off?
Moving X Months Avg = CALCULATE(AVERAGE(FactInternetSales21[SalesAmount]),DATESINPERIOD(FactInternetSales21[OrderDate],LASTDATE(FactInternetSales21[OrderDate]),-3,day))
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 🙂
6 Replies
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 🙂
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?
