Rolling Last 3 Months Using Dates In Period

[sfalk781]

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))

[Fowmy · Accepted solution]

@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 🙂

YouTube  LinkedIn

[sfalk781]

@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]

@Fowmy  I also wanted to ask, in your formulary, what is actually happening with "amount".

[Fowmy]

@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 🙂

YouTube  LinkedIn

[sfalk781]

@Fowmy  Thanks alot.  I looked at the source data which is ficticious data that I was testing with and saw that you were right, there are multiple days of data.  My visual was aggregating by total sales amount.  As far as the variable "amount" goes, is it standard to use that in all summarize functions?

[Fowmy]

@sfalk781 

We use SUMMARIIZE column(s) to create a virtual table, it is optional to add an aggegation column along with it like I did with a variable AMOUNT.  You can use any name for it. 

please refer support documents for further understanding 

________________________

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 🙂

YouTube  LinkedIn