Forum Discussion
homboy27
Helper III
Helper IIILast 2 weeks average/ employee
Please see below data. I am calculating total sales divided by total hours in column I by employee. But I would like to calculate last 2 weeks average by employee in column J. So for example in ...
parry2k
Super User
Super Userhomboy27 change the DAX measure to this, and replace the value of __lastHowManyWeeks with 8 or whatever value you want, you can also make it dynamic:
Last x Weeks Average =
VAR __LastHowManyWeeks = 2 --change this to number of weeks you are looking for.
VAR __WeekEnding = MAX ( 'Table'[Week Ending] )
VAR __WeekStart = __WeekEnding - ( ( __LastHowManyWeeks - 1 ) * 7 )
RETURN
AVERAGEX (
FILTER (
ALLSELECTED ( 'Table'[Week Ending] ),
'Table'[Week Ending] >= __WeekStart && 'Table'[Week Ending] <= __WeekEnding
),
[% Hours]
)
homboy27Thank you, what do you mean by make it more dynamic?