Get the average per group for the last 6 months

[Anonymous]

Hi all, 

I have a table with one line for each user for each daily login, next to it I have the User ID and the Department.

Can someone help me with making a measure that calculates the average of logins per department per month? And then compare the user logins in the current month with the average of their department's logins of the previous 6 months?

I am attaching a pbix sample file of my dataset https://drive.google.com/file/d/1lgPt5CLsYpUQz6EmsjWreh9u0abQacjy/view?usp=sharing

[Anonymous]

@amitchandak Thank you but my answer was how to calculate the averages per department and then compare it against the single user, so for example, if department A had a Login average of 7 logins in the last 6 months, how does that compare against the logins for this month of a user part of Department A?

[v-xiaosun-msft]

Hi @Anonymous ,

You can add EARLIER function in the filters of the CALCULATE function so that you can get each department's average of logins.

'Table'[department]=EARLIER( 'Table'[department])

The same to per user.

'Table'[User ID]=EARLIER( 'Table'[User ID])

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-xiaosun-msft could you write down what would be the full formula? So I can calculate the user logins in the current month compared to the average of their department logins in the 6 months before.

[amitchandak]

@Anonymous , Example of 6 month average, with help from date table

12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Count('Table'[User ID)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.