Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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;
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
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,
Is it possible to create that type of formula in my Staff_Starters table as it is connected to a Date table?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |