cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SofG
Frequent Visitor

Headcount measure or new table?

Hi,

 

I am new to Power BI and I am struggeling with my head count formula. 

 

I have one table with a list of employees, start and end date (where of course some are blank since they are still employed) and one separate calendar table.

 

I wish to calculate number of contracts to see how many people we are at a specific time. I want to calculate the number of people at the end of a month, using Hierarchy calendar on month, quarter and year to filter. I wish for the formula to always count the latest option in the fitlering. I.e. if I choose FY2019 and my latest data is from august, it should count to august. (therefore august and 2019 should provide similar numbers?) I believe that the formula I use right now is wrong, because the head count vary a lot from month to month. 

 

I'm not sure if it matters, but there are no active relations between calendar and people data. However, there are indirect ones through other tables. 

 

Can anyone give me a tip on how to solve this?

 

Can I use formulas or should i try to merge the tables?

 

I'm thankful for any help I can get!

 

Kind regards

 

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @SofG ,

 

We can create a measure to meet your requirement:

 

HeadCount = 
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        OR (
            [Employent end date] >= MAXX ( FILTERS ( 'Calendar'[Date] ), [Date] ),
            ISBLANK ( 'Table'[Employent end date] )
        )
    )
)

11.PNG12.PNG

 

 

 

BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
GeraldZ
Helper IV
Helper IV

Can you post a sample of your data as well as maybe what you would like it to look like in excel?

SofG
Frequent Visitor

Hi GeraldZ,

 

Unfortunately my data is confidential. But here is a snapshot of what my people data table contains.

 

Capture2.PNG

 

Whould it help if I created fictive data for you to look at?

 

Here is the code for the calendar I use:

 

let
    ListDates = List.Dates(#"Calendar Start Date", Number.From(#"Calendar End Date" - #"Calendar Start Date")+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed to Date" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"---Added Calendar Columns---" = #"Changed to Date",
    #"Inserted Year" = Table.AddColumn(#"---Added Calendar Columns---", "Year", each Date.Year([Date]), type number),
    #"Added Year Short" = Table.AddColumn(#"Inserted Year", "Year Short", each Text.End(Text.From(Date.Year([Date]), "en-US"), 2), type text),
    #"Inserted Month" = Table.AddColumn(#"Added Year Short", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Month Name Short" = Table.AddColumn(#"Inserted Month Name", "Month Name Short", each Text.Start(Date.MonthName([Date]), 3), type text),
    #"Inserted Month & Year" = Table.AddColumn(#"Inserted Month Name Short", "Month & Year", each Number.ToText([Year]) & (if [Month] < 10 then "0" else "") & Number.ToText([Month])),
    #"Inserted Month & Year Short" = Table.AddColumn(#"Inserted Month & Year", "Month & Year Short", each Text.Combine({
Text.Start(Date.MonthName([Date]), 3),
" ",
Text.End(Text.From(Date.Year([Date]), "en-US"), 2)
}), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month & Year Short", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Quarter Name" = Table.AddColumn(#"Inserted Quarter", "Quarter Name", each "Q"&Text.From(Date.QuarterOfYear([Date])), type text),
    #"---Added Fiscal Columns---" = #"Inserted Quarter Name",
    #"Inserted Fiscal Month" = Table.AddColumn(#"---Added Fiscal Columns---", "Fiscal Month", each if Date.Month([Date]) > Date.Month(#"Fiscal Year End Date")
then Date.Month([Date]) - Date.Month(#"Fiscal Year End Date")
else Date.Month([Date]) + (12 - Date.Month(#"Fiscal Year End Date")), Int64.Type),
    #"Inserted Fiscal Month Name" = Table.AddColumn(#"Inserted Fiscal Month", "Fiscal Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Fiscal Quarter" = Table.AddColumn(#"Inserted Fiscal Month Name", "Fiscal Quarter", each Number.RoundUp((if Date.Month([Date]) > Date.Month(#"Fiscal Year End Date")
then Date.Month([Date]) - Date.Month(#"Fiscal Year End Date")
else Date.Month([Date]) + (12 - Date.Month(#"Fiscal Year End Date"))) / 3, 0)),
    #"Inserted Fiscal Quarter Name" = Table.AddColumn(#"Inserted Fiscal Quarter", "Fiscal Quarter Name", each "Q" & 
Text.From(
Number.RoundUp((if Date.Month([Date]) > Date.Month(#"Fiscal Year End Date")
then Date.Month([Date]) - Date.Month(#"Fiscal Year End Date")
else Date.Month([Date]) + (12 - Date.Month(#"Fiscal Year End Date"))) / 3, 0)
)),
    #"Inserted Fiscal Year" = Table.AddColumn(#"Inserted Fiscal Quarter Name", "Fiscal Year", each if Date.Month([Date]) > Date.Month(#"Fiscal Year End Date") then Date.Year([Date]) + 1 else Date.Year([Date]), Int64.Type),
    #"Inserted Fiscal Year Name" = Table.AddColumn(#"Inserted Fiscal Year", "Fiscal Year Name", each Text.Combine({
"FY",
Text.End( 
     Text.From( if Date.Month([Date]) > Date.Month(#"Fiscal Year End Date") then Date.Year([Date]) + 1 else Date.Year([Date])
     ), 
2)
}), type text),
    #"---Added OFFSET Columns---" = #"Inserted Fiscal Year Name",
    #"Inserted Day Offset" = Table.AddColumn(#"---Added OFFSET Columns---", "Day Offset", each Number.From((Date.From(DateTime.LocalNow()) - [Date])) * -1, Int64.Type),
    #"Inserted Week Offset" = Table.AddColumn(#"Inserted Day Offset", "Week Offset", each (Number.From((Date.From(Date.EndOfWeek(DateTime.LocalNow(), Day.Monday)) - 
Date.EndOfWeek([Date], Day.Monday))) * -1) / 7, Int64.Type),
    #"Inserted Month Offset" = Table.AddColumn(#"Inserted Week Offset", "Month Offset", each (Date.Year([Date]) - Date.Year(Date.From(DateTime.LocalNow())))*12 + Date.Month([Date]) - Date.Month(Date.From(DateTime.LocalNow())), Int64.Type),
    #"Inserted Quarter Offset" = Table.AddColumn(#"Inserted Month Offset", "Quarter Offset", each /*Year Difference*/
       ( Date.Year([Date]) - Date.Year(DateTime.LocalNow()) )*4
       /*Quarter Difference*/
      + Number.RoundUp(Date.Month([Date]) / 3) 
      - Number.RoundUp(Date.Month(Date.From(DateTime.LocalNow()) ) / 3), Int64.Type),
    #"Inserted Year Offset" = Table.AddColumn(#"Inserted Quarter Offset", "Year Offset", each (Date.Year(Date.From(DateTime.LocalNow())) - Date.Year([Date])) * -1, Int64.Type),
    #"Inserted Fiscal Year Offset" = Table.AddColumn(#"Inserted Year Offset", "Fiscal Year Offset", each //Get Current Row Fiscal Year
(if Date.Month([Date]) > Date.Month(#"Fiscal Year End Date") then Date.Year([Date]) + 1 else Date.Year([Date]))
-
//Get Today's Date Fiscal Year
(
if    Date.Month(Date.From(DateTime.LocalNow())) > Date.Month(#"Fiscal Year End Date") 
then   Date.Year(Date.From(DateTime.LocalNow())) + 1 
else    Date.Year(Date.From(DateTime.LocalNow()))
), Int64.Type),
    #"---Added General Columns---" = #"Inserted Fiscal Year Offset",
    #"Inserted Day" = Table.AddColumn(#"---Added General Columns---", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day Of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Weekend Flag" = Table.AddColumn(#"Inserted Day of Week", "Weekend Flag", each Logical.From(if Date.DayOfWeek([Date]) = 6 or Date.DayOfWeek([Date]) = 0 then 1 else 0), type logical),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Weekend Flag", "End Of Week", each Date.EndOfWeek([Date], Day.Monday), type date),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted End of Week",{{"Date", type text}})
in
    #"Changed Type"

Just to clarify, this is for Power BI. And I just wish to see a number acoring to my filtering. Not a table or a graph. 

 

Thank you!

 

v-lid-msft
Community Support
Community Support

Hi @SofG ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lid-msft
Community Support
Community Support

Hi @SofG ,

 

We can create a measure to meet your requirement:

 

HeadCount = 
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        OR (
            [Employent end date] >= MAXX ( FILTERS ( 'Calendar'[Date] ), [Date] ),
            ISBLANK ( 'Table'[Employent end date] )
        )
    )
)

11.PNG12.PNG

 

 

 

BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors