cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
libratic Frequent Visitor
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
Highlighted
Community Support Team
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.

3.PNG

 

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
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!
libratic Frequent Visitor
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:

 

 MonthSales Action
Current monthJuly11Exclude (in progress)
1June20Exclude (Previous Month)
2May21 
3April9MIN Sales (Exclude) 
4March19 
5February71MAX Sales (Exclude)
6January45 
7December35 
    
 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 DateAmount
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!!

Highlighted
Community Support Team
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.

3.PNG

 

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: | |
libratic Frequent Visitor
Frequent Visitor

Re: Last 7 Month Avg. Minus Min/Max

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