cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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?

Capture.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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. 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User
Super User

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

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Anonymous
Not applicable

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

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...

Super User
Super User

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

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...


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Anonymous
Not applicable

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

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

Person120181JanuaryJanuary 20181
Person120182FebruaryFebruary 20181
Person120183MarchMarch 20181
Person120184AprilApril 20181
Person120185MayMay 20181
Person120186JuneJune 20181
Person120187JulyJuly 20181
Person120188AugustAugust 20181
Person120189SeptemberSeptember 20181
Person1201810OctoberOctober 20181
Person1201811NovemberNovember 20181
Person1201812DecemberDecember 20181
Person120191JanuaryJanuary 20191
Person120192FebruaryFebruary 20191
Person120193MarchMarch 20191
Person120194AprilApril 20191
Person120195MayMay 20191
Person120196JuneJune 20190
Person120197JulyJuly 20190
Person120198AugustAugust 20190
Person120199SeptemberSeptember 20190
Person1201910OctoberOctober 20190
Person1201911NovemberNovember 20190
Person1201912DecemberDecember 20190
Person220181JanuaryJanuary 20181
Person220182FebruaryFebruary 20181
Person220183MarchMarch 20181
Person220184AprilApril 20181
Person220185MayMay 20181
Person220186JuneJune 20181
Person220187JulyJuly 20181
Person220188AugustAugust 20181
Person220189SeptemberSeptember 20181
Person2201810OctoberOctober 20181
Person2201811NovemberNovember 20180
Person2201812DecemberDecember 20180
Person220191JanuaryJanuary 20190
Person220192FebruaryFebruary 20190
Person220193MarchMarch 20190
Person220194AprilApril 20190
Person220195MayMay 20190
Person220196JuneJune 20190
Person220197JulyJuly 20190
Person220198AugustAugust 20190
Person220199SeptemberSeptember 20190
Person2201910OctoberOctober 20190
Person2201911NovemberNovember 20190
Person2201912DecemberDecember 20190
Super User
Super User

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

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. 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 66 members 1,198 guests
Please welcome our newest community members: