Average

[SLima]

Good afternoon.

How do I calculate the average purchase of the past months to customer, starting from the selected month?

example.

I selected june/2021 he would make the average of may, april, march, february and january of that same year.

 

Thank you in advance.

[OwenAuger]

Hi @SLima 

Assuming you have a Date table set up with a YearMonth column (or similar), something along these lines should do the trick:

Average Purchases over 5 preceding months = 
CALCULATE ( 
    AVERAGEX ( 
        VALUES ( 'Date'[YearMonth] ),
        [Purchases]
    ),
    DATESINPERIOD (
        'Date'[Date],
        EOMONTH ( MAX ( 'Date'[Date] ), -1 ),
        -5,
        MONTH
    )
)

[SLima]

it would not be the previous 5 months, it would be from the selected month/year

[OwenAuger]

Yes, in the code above, the maximum visible date is used as the reference point for determining the date filter for the "previous" 5 months.

You can change MAX ( 'Date'[Date] ) to TODAY() for the date filter to be determined relative to the current date (when the query is generated from the report page):

Average Purchases over 5 preceding months =
CALCULATE (
    AVERAGEX (
        VALUES ( 'Date'[YearMonth] ),
        [Purchases]
    ),
    DATESINPERIOD (
        'Date'[Date],
        EOMONTH ( TODAY (), -1 ),
        -5,
        MONTH
    )
)

[SLima]

I did it that way

Sales Average last months = 
CALCULATE ( 
    AVERAGEX ( 
        VALUES ( dCalendario[MonthYear] ),
        [Sales]
    ),
    DATESINPERIOD (
        ( dCalendario[date]),
        EOMONTH ( MAX ( dCalendario[date] ), -1 ),
        SWITCH(VALUES(dCalendario[MonthNum]),
        12,-11,
        11,-10,
        10,-9,
        9,-8,
        8,-7,
        7,-6,
        6,-5,
        5,-4,
        4,-3,
        3,-2,
        2,-1),
        MONTH
    )
)