Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 Em...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    thanks for quick help.

    However the result seems decreasing and the value is too high.