topic Average in DAX Commands and Tips

Average

SLima — Thu, 10 Jun 2021 01:04:56 GMT

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.

Re: Average

OwenAuger — Sat, 12 Jun 2021 00:40:38 GMT

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

Re: Average

SLima — Fri, 11 Jun 2021 15:12:56 GMT

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

Re: Average

OwenAuger — Sat, 12 Jun 2021 00:41:59 GMT

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

Re: Average

SLima — Sat, 12 Jun 2021 16:09:00 GMT

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