Forum Discussion
DAX Help : Previous 3 Months average
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])
Community ChampionAnonymous
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
)
Hi SpartaBI , sincerrely apologise to interrupt you again.
I have face another difficulties that is similar to this question. However, in the current case, I would like to created visual table and add a measure. For example, based on the visual below, the light blue color bar can be refer to the average of the lead times (which i can just drag the column into the visual and click calculate average); the main problem that I face, is that how am i able to show the average lead time of the previous 3 month in the line value, for instance, the line chart of april should show 19 ,may is 22 , june is 15. Thanks you so much for your assistance
- SpartaBI
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])Hi SpartaBI , thanks for the clarification !!! appreciate!
- SpartaBI
Anonymous my pleasure. Please also mark the previous message as a solution for community visabilty.
P.S.
Check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂