Forum Discussion
Cumulative Average Months from join date
Hi,
I need to get the average numbers of month from Report date based on selected date filter.
| Employee | Report date |
| Emp1 | 1/1/2020 |
| Emp2 | 1/1/2020 |
| Emp3 | 2/1/2020 |
| Emp4 | 3/1/2020 |
| Emp5 | 4/1/2020 |
Now what i need is to get the datediff from report date vs date on the slicer and then average it.
For example, i filter Feb2020:
Headcount is 3, but my average month is 1.33 - because month diff from Feb2020 of Emp1 and Emp2 is both 2, while Emp3 is 0.
Average of 2,2,0 is 1.33
Now if i filter date to Apr2020:
Headcount is 5, then my average month is 2.22 - because month diff from Apr2020 of Emp1 and Emp2 is both 4, Emp3 is 2, Emp4 is 1, and Emp5 is 0.
Average of 4,4,2,1,0 is 2.22
Here's the output im trying to get
| Headcount YTD | 2 | 3 | 4 | 5 | 5 | 5 |
| Cumulative Ave month | 1 | 1.33 | 1.75 | 2.2 | 3.2 | 4.2 |
| Month | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 |
Hi Anonymous ,
Actually I‘m a little confused about your issue...
What does Headcount mean? Is the 4 you described in this "because month diff from Apr2020 of Emp1 and Emp2 is both 4" maybe 3= 2020/4 -2020/1 ?
If you want to cumulative Average Months , you could create measure(diff) about Month interval, then use divide calculate average. the following formula to create a measure :step 1: Add Measure (diff )
diff = VAR _maxdate = CALCULATE ( MAX ( [Report date] ), ALLSELECTED ( 'Date' ) ) RETURN DATEDIFF ( MAX ( 'Date'[Report date] ), _maxdate, MONTH )step 2: Add measure(Average )
Average = DIVIDE(SUMX('Date',[diff]),COUNTROWS('Date'))
The final output is shown below:
Or could you please share more detail information to help us clarify your scenario?
Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
v-yalanwu-msftCommunity Support
Hi Anonymous ,
Actually I‘m a little confused about your issue...
What does Headcount mean? Is the 4 you described in this "because month diff from Apr2020 of Emp1 and Emp2 is both 4" maybe 3= 2020/4 -2020/1 ?
If you want to cumulative Average Months , you could create measure(diff) about Month interval, then use divide calculate average. the following formula to create a measure :step 1: Add Measure (diff )
diff = VAR _maxdate = CALCULATE ( MAX ( [Report date] ), ALLSELECTED ( 'Date' ) ) RETURN DATEDIFF ( MAX ( 'Date'[Report date] ), _maxdate, MONTH )step 2: Add measure(Average )
Average = DIVIDE(SUMX('Date',[diff]),COUNTROWS('Date'))
The final output is shown below:
Or could you please share more detail information to help us clarify your scenario?
Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- amitchandak
Super User
Anonymous , Try a measure like
divide(sumx(values(table[employee]), datediff( eomonth(maxx(date[date]),-1)+1,min([Report date]), day)), count(Table[Employee]))
or with a selected date
measure =
var _max = maxx(allselected(Date), Date[Date]) //slicer date
return
divide(sumx(table, datediff([Report date], _max, day)), count(Table[Employee]))
