Cumulative Average Months from join date

[John_Dozen34999]

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

[v-yalanwu-msft · Accepted solution]

Hi @John_Dozen34999 ,

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.

[John_Dozen34999]

Amazing! this is the one. Sorry to confuse you, but you got it! 🙂

Is there a way to use this solution in a trend visual, like bar chart?

[amitchandak]

@John_Dozen34999 , 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]))

[John_Dozen34999]

thanks for quick help.

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