cancel
Showing results for
Did you mean:
Frequent Visitor

## Last 7 Month Avg. Minus Min/Max

Hey Everyone,

I am running into a wall figuring something out in DAX. For our sales purposes, I need to figure out the following formula:

(Last 7 Months of Data - Exclude Most Recent Month - MAX Month - MIN Month) / 4

Any help would be greatly appreciated! Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Last 7 Month Avg. Minus Min/Max

Hi @libratic,

Your scenario: get the average result from last 7 month, exclude current month, previous month, min and max amount.

Logic : summary records by year month, get records between last 7 month and last 2 month(remove current and previous amount), then remove max and min value and get the average amount.

If this is a case, you can try to use below formula:

Sample table.

Sample measure:

```Last 7 Month Average =
var maxDate=MAX('Sample Table'[Date])
var temp= SUMMARIZE(FILTER(ALL('Sample Table'),[Date]>=DATE(YEAR(maxDate),MONTH(maxDate)-7,1)&&[Date]<DATE(YEAR(maxDate),MONTH(maxDate)-1,1)),[Date].[Year],[Date].[MonthNo],"Total",SUM([Amount]))
return
(SUMX(temp,[Total])-MAXX(temp,[Total])-MINX(temp,[Total]))/4```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
4 REPLIES 4
Super User

## Re: Last 7 Month Avg. Minus Min/Max

Hey,

I have to admit that I'm not fully understand what you are looking for, for this reason I want to know if my understanding is correct:

a) body of data
data available for the months Nov 2016 - June 2017, just one measure amount

Last 7 Months of data: Dec 2016 - June 2017 --> A = sum(amount) of the 7 month

Most Recent Month: June 2017 --> B = sum(amount) of June 2017

Max Month: ??? --> C

Min Month: D = sum(amount) of November 2016

Result = (A - B - C - D)/4

Guess, you need to put me on the right track

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Frequent Visitor

## Re: Last 7 Month Avg. Minus Min/Max

Hey Tom,

Sorry, I definitely wasn't clear in my first post. Here's a quick little diagram of what I need, hope it makes more sense:

 Month Sales Action Current month July 11 Exclude (in progress) 1 June 20 Exclude (Previous Month) 2 May 21 3 April 9 MIN Sales (Exclude) 4 March 19 5 February 71 MAX Sales (Exclude) 6 January 45 7 December 35 Sum: 120 Sum/4: 120/4 Output: 30

My data is in the following format, and goes back to 2014, I am trying to only look at the previous 7 months worth of data.

 Sales Date Amount 7/24/2017 -\$1,055.28 7/24/2017 \$1,275.00 7/24/2017 \$1,295.00 7/24/2017 \$1,295.00 7/24/2017 \$1,275.00 7/24/2017 \$592.00 7/23/2017 \$5,100.00 7/23/2017 \$1,295.00 7/23/2017 \$1,295.00

In plain english, I'm looking to average out the last 7 months, excluding the current & previous month, and excluding the months with the most and least sales. And YES, the solution you posted above would work amazingly, if I could translate that into DAX, except for MIN, I would need the month with the least sales, not the last month.

Thanks in advance for the help!!

Community Support Team

## Re: Last 7 Month Avg. Minus Min/Max

Hi @libratic,

Your scenario: get the average result from last 7 month, exclude current month, previous month, min and max amount.

Logic : summary records by year month, get records between last 7 month and last 2 month(remove current and previous amount), then remove max and min value and get the average amount.

If this is a case, you can try to use below formula:

Sample table.

Sample measure:

```Last 7 Month Average =
var maxDate=MAX('Sample Table'[Date])
var temp= SUMMARIZE(FILTER(ALL('Sample Table'),[Date]>=DATE(YEAR(maxDate),MONTH(maxDate)-7,1)&&[Date]<DATE(YEAR(maxDate),MONTH(maxDate)-1,1)),[Date].[Year],[Date].[MonthNo],"Total",SUM([Amount]))
return
(SUMX(temp,[Total])-MAXX(temp,[Total])-MINX(temp,[Total]))/4```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Highlighted
Frequent Visitor

## Re: Last 7 Month Avg. Minus Min/Max

v-shex-msft, that worked like a charm, thank you so much!!