Forum Discussion
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!
Hi Anonymous,
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]))/4Regards,
Xiaoxin Sheng
4 Replies
- TomMartens
Super User
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 amountLast 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
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!!
Hi Anonymous,
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]))/4Regards,
Xiaoxin Sheng
