Last 7 Month Avg. Minus Min/Max

[libratic]

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!

[TomMartens]

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

[libratic]

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

[v-shex-msft · Accepted solution]

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

[libratic]

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