Count employees in Year-Mo format by Month or Quarter or Year

[Anonymous]

Hi everyone,

I have a set of monthly employee records and I'm looking to create a measure that will allow me count/average the number of employees by Month or Year or Quarter depending on which Date Table fields I put into the Columns. 

My data looks like this:

Year-Mo	Name	Team
202006	Bob	Alpha
202006	George	Alpha
202006	Steve	Alpha
202006	Sam	Alpha
202006	Alice	Alpha
202006	Jane	Bravo
202006	Kate	Bravo
202006	Rick	Bravo
202005	Bob	Alpha
202005	George	Alpha
202005	Steve	Alpha
202005	Sam	Alpha
202005	Alice	Bravo
202004	Bob	Alpha
202004	George	Alpha
202004	Steve	Alpha
202004	Sam	Alpha

My desire would be to create outputs like this:

Output 1

(Straight count for monthly)

	202004	202005	202006
Alpha	4	4	5
Bravo	0	1	3

Output 2

(Average for quarter or year)

	Q2-2020
Alpha	4.33
Bravo	1.33

I am able to get Output 1 using a calculate(count) measure and linking the Year-Mo to my date table. However, I'm unsure how to do the second one -- if I change the Column to Quarter, my output is a sum of the Year-Mo data and not an average. 

Thank you!

[edhans · Accepted solution]

Hi @Anonymous 

You are on the right track with a date table. I added a DateKey field to your data using the year and month. I just made it the first of the month then joined to the date table. Then I used this measure to get the average. Note that the totals in the matrix may not appear correct, but it is looking at the total year. It may not be relevant, in which case I'd just remove the total if the annual total average isn't helpful.

This would be fully dynamic. You could drop months, quarters, semesters, whatever and it would continue to show th average per month. It wouldn't work for days and weeks as those are below the granularity of what you are measuring - monthly average.

Average Teams = 
VAR TeamCount = COUNTROWS(Teams)
VAR MonthCount = DISTINCTCOUNT('Date'[Month])
RETURN
    DIVIDE(
        TeamCount,
        MonthCount,
        0
    )

It returns this:

You can see what I did in the DateKey field in Power Query, and how my date table is structured in this PBIX file.

Creating a Dynamic Date Table in Power Query

[Anonymous]

Thank you very much @edhans    

This works beautifully 🙂

[edhans]

Great @Anonymous - glad it helped.

[ryan_mayu]

@Anonymous 

First create a new column.

year-Q = 
VAR month=RIGHT(Sheet9[Year-Mo],2)
VAR Q = if(month in {"01","02","03"},"Q1",if(month in {"04","05","06"},"Q2",if(month in {"07","08","09"},"Q3","Q4")))
return left(Sheet9[Year-Mo],4)&Q

Then create a measure

average = COUNT(Sheet9[Name])/3

[Anonymous]

Thanks @ryan_mayu 

I wonder if there's a way to do this more dynamically? Ultimately this is going to be a tool for others to use and if I can get away with using a single measure that can count if we are looking at it monthly and average if we are looking at it quarterly or yearly, that would be very useful so as people build their own reports, it's a single "Employee count" measure vs. having three separate measures for monthly, quarterly, and yearly counts. 

I have a date table in my model that have things like "Year-Mo", "Quarter-Year", "Year" defined. My ideal would if then if I drop in "Year-Mo" into the column, it'll do the simple count of the Year-Mo employee table; if I drop in "Quarter-Year", it'll do the average. Is that possible? 

Thanks in advance!