Headcount reporting (show end of period value instead of sum)

[Anonymous]

Hello all,

I want to create an overview of the headcount numbers by year with an option to drill down to the month.

Problem is that by default, Power BI takes the sum of the values. So 1 person would for example show as "12" when I do not pull the month in the visual.

Is there a way to tell PowerBI to show only the value of the last period - so for a year it is December value, for a Quarter it is the 3rd month?

[Greg_Deckler]

You can use MIN or MAX but I would go with AVERAGE. You can change the aggregation by clicking the drop down arrow for the column in the Fields area or change the default aggregation on the Modeling tab.

[Anonymous]

Thanks Greg, the average is a nice feature already but I would really need the "end of period" values, so that each person can only be 1 if he's in or 0 if he's not...

[Greg_Deckler]

You will need a numeric month. You will use that to in a MAX. You can then FILTER down to the correct row that you want. So, for example, if you are at the entire year level, MAX will return 12 for December. Then you can filter down to that month for each person to see if they are "in" or not. Same logic will work at the Quarter level and Month level.

See if my Time Intelligence the Hard Way provides the jist of things.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Thanks, I'm not sure I'm advanced enough to find my awswer there. Could you give me an idea of the formulas/measures/columns I need for a dataset like this?

NameYearMonth numberMonthPeriodHeadcount

Person1	2018	1	January	January 2018	1
Person1	2018	2	February	February 2018	1
Person1	2018	3	March	March 2018	1
Person1	2018	4	April	April 2018	1
Person1	2018	5	May	May 2018	1
Person1	2018	6	June	June 2018	1
Person1	2018	7	July	July 2018	1
Person1	2018	8	August	August 2018	1
Person1	2018	9	September	September 2018	1
Person1	2018	10	October	October 2018	1
Person1	2018	11	November	November 2018	1
Person1	2018	12	December	December 2018	1
Person1	2019	1	January	January 2019	1
Person1	2019	2	February	February 2019	1
Person1	2019	3	March	March 2019	1
Person1	2019	4	April	April 2019	1
Person1	2019	5	May	May 2019	1
Person1	2019	6	June	June 2019	0
Person1	2019	7	July	July 2019	0
Person1	2019	8	August	August 2019	0
Person1	2019	9	September	September 2019	0
Person1	2019	10	October	October 2019	0
Person1	2019	11	November	November 2019	0
Person1	2019	12	December	December 2019	0
Person2	2018	1	January	January 2018	1
Person2	2018	2	February	February 2018	1
Person2	2018	3	March	March 2018	1
Person2	2018	4	April	April 2018	1
Person2	2018	5	May	May 2018	1
Person2	2018	6	June	June 2018	1
Person2	2018	7	July	July 2018	1
Person2	2018	8	August	August 2018	1
Person2	2018	9	September	September 2018	1
Person2	2018	10	October	October 2018	1
Person2	2018	11	November	November 2018	0
Person2	2018	12	December	December 2018	0
Person2	2019	1	January	January 2019	0
Person2	2019	2	February	February 2019	0
Person2	2019	3	March	March 2019	0
Person2	2019	4	April	April 2019	0
Person2	2019	5	May	May 2019	0
Person2	2019	6	June	June 2019	0
Person2	2019	7	July	July 2019	0
Person2	2019	8	August	August 2019	0
Person2	2019	9	September	September 2019	0
Person2	2019	10	October	October 2019	0
Person2	2019	11	November	November 2019	0
Person2	2019	12	December	December 2019	0

[Greg_Deckler · Accepted solution]

OK, sample data is soooooo much better!! Alright, you just need a Period column which is a variation on my Quarters quick measure. https://community.powerbi.com/t5/Quick-Measures-Gallery/Quarter/m-p/391541

Period = CONCATENATE([Year] & "Q",ROUNDUP(MONTH([Date])/3,0))

And then an HC measure like this, which is really sort of a variation of my Time Intelligence the Hard Way quick measure. https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

HC = 
VAR __year = MAX([Year])
VAR __month = MAX([MonthNum])
VAR __table = FILTER(ALL('Table13'),[Year] = __year && [MonthNum] = __month)
RETURN
SUMX(__table,[Present])

See Page 9, Table 13 of attached.