Forum Discussion
DAX Help : Previous 3 Months average
Hi all, hope you are doing well
I'm trying to create a measure to get Previous 3 month average, yet could not figure out on how to do this in DAX. For example, based on the the picture attahced below:
-Jan can be blank
-Febuary can be blank
-March can be blank
-April onwards will sum every number from January to March and devide by 3, because we are looking at the average of previous 3 month.
-May will sum every number from February to April and devide by 3
Any help is very much appreciated. Thank you in advance.
Anonymous
Average of Previous 3 Full Months = VAR _current_date = 'Table'[GRN Date] VAR _end_point = EOMONTH(_current_date, -1) VAR _start_point = EOMONTH(_current_date, -4) + 1 VAR _min_date_total = MIN('Table'[GRN Date]) VAR _result = DIVIDE( SUMX( FILTER( 'Table', 'Table'[GRN Date] >= _start_point && 'Table'[GRN Date] <= _end_point ), 'Table'[Lead Time (Days)] ), 3 ) RETURN IF( EOMONTH(_start_point, -1) >= EOMONTH(_min_date_total, -1), _result )Anonymous we are not dealing here with best practice stuff, but, as we created the column before you can create this measure to use as the line value:
Average of Previous 3 Full Months Measure Dependant on Coloumn =AVERAGE('Table '[Average of Previous 3 Full Months Calculated Column])
10 Replies
- SpartaBI
Community Champion
Anonymous in your photo is that a data table and you need a calculated column or that is a visual table and you want to add a measure? If it's a visual what is the measure you have there for Lead Time (Days)?
- SpartaBI
Anonymous
Average of Previous 3 Full Months = VAR _current_date = 'Table'[GRN Date] VAR _end_point = EOMONTH(_current_date, -1) VAR _start_point = EOMONTH(_current_date, -4) + 1 VAR _min_date_total = MIN('Table'[GRN Date]) VAR _result = DIVIDE( SUMX( FILTER( 'Table', 'Table'[GRN Date] >= _start_point && 'Table'[GRN Date] <= _end_point ), 'Table'[Lead Time (Days)] ), 3 ) RETURN IF( EOMONTH(_start_point, -1) >= EOMONTH(_min_date_total, -1), _result )