How to work out total headcount per Month using two date columns?

[rush]

Hi

I have a table called Staff_Starters (Table) which is connected to a Date dimension.

I got the formula which calculates the headcount per month but does it in a calculatable table & but does not include other columns like FullName, position, etc which I would need to create a relationship with my other tables.

Is it possible to add it to my Staff_Starters as a calculated column or measure?

I am not sure if the formula below is calculating correctly as it uses the columns below;

• DateEmployed
• TerminationDate

The formula counts everyone that has a DateEmployed & stops counting going forward when it has a TerminationDate.

NB: My TerminationDate for active staff is now set to 1899/12/31

Total Headcount = 
var MinDate = FIRSTDATE('Staff_Starters'[Date Employed])
var Result =
SUMMARIZE(
    FILTER(
        CROSSJOIN(
            ADDCOLUMNS('Staff_Starters',"End Date",if([TerminationDate]=DATE(1899,12,31),DATE(3000,01,01),[TerminationDate])),
            ADDCOLUMNS(
                CALENDAR(
                    MinDate,
                    TODAY()
                ),
				"Active Month",DATE(YEAR([Date]),MONTH([Date]),1)
            )
		)
            , [Date] >= [Date Employed]
            && [Date] <= [End Date])
        ,[Active Month],
        "Head Count", COUNT(Staff_Starters[StaffID])
        )
return Result

[v-sihou-msft]

@rush

In your source table, the data granularity is on employee level. However, your total head count calcuation in your calculated table is on Month Level. Since these two tables are under different granularity, it's not possbile to build relationship between them or add related column from one table into another.

Regards,

[rush]

Hi @v-sihou-msft

Is it possible to create that type of formula in my Staff_Starters table as it is connected to a Date table?