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 All,
I am trying to work on a consolidated report which shows beginning headcount, Newhires and other details for each period and year.
I have lookup values for year, period, start and end date of period in ‘PeriodTable’. And Assignment ID, Assighnment Start Date,Assignment End Date etc in ‘Assignmnet table’.
I would like to make the aggregations for each period for selected year from period table
I need your suggetions to create a calculated columns/measures for below two conditions as there is no direct relationship bewteen my Period Table and Assignment Table–
The final report should look like below –
Period | Beginning Head Count | New hires |
1 | 512 | 21 |
2 | 254 | 24 |
3 | 269 | 12 |
4 | 657 | 9 |
Any help would be much appreciated.
Thank you
You may add measures as shown below.
Beginning Head Count = VAR startDate = MAX ( Period[start date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Assignment[Assignment ID] ), FILTER ( Assignment, Assignment[Start Date] < startDate && ( ISBLANK ( Assignment[End Date] ) || Assignment[End Date] >= startDate ) ) )
New hires = VAR startDate = MAX ( Period[start date] ) VAR endDate = MAX ( Period[end date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Assignment[Assignment ID] ), FILTER ( Assignment, Assignment[Start Date] >= startDate && Assignment[Start Date] <= endDate ) )
Hello @v-chuncz-msft
Thanks for your reply,
I am trying the measure as you suggested on test report but the results are not as intended.
As shown in first picture, if I am not selecting any year I get my beginning headcount which doesn’t match with the actual data,
And If I select any year from dropdown, I only can see my New Hires count for that number.
For this report, I want the cumulative beginning headcount and only new hires number for each period as per selected year, and I don’t know how to get this in one table. Can you please help me with this
Note: I have established connection between 'Period' table and 'Assignment' table by creating a 'Date ‘Column in my period table using power Query as (1 to M between Period[Date] to Assignment[StartDate])
Thank you.
Please share us your simplified model, then we can have a test.
Hello @v-chuncz-msft,
Here is sample Data Model for HeadCount Report.
AssignmentTable
Id | AssignmentStartDate | AssignmentEnddate |
1 | 12/17/2014 | 02/15/2015 |
2 | 01/05/2015 | null |
3 | 01/12/2015 | 04/30/2015 |
4 | 01/21/2015 | 04/23/2015 |
5 | 02/16/2015 | null |
6 | 02/23/2015 | 07/31/2015 |
Period Table
Year | Period | PeriodStartDate | PeriodEndDate | Date |
2014 | 13 | 11/24/2014 | 12/21/2014 | 12/17/2014 |
2015 | 1 | 12/22/2014 | 01/18/2015 | 01/05/2015 |
2015 | 1 | 12/22/2015 | 01/18/2015 | 01/12/2015 |
2015 | 2 | 01/19/2015 | 02/15/2015 | 01/21/2015 |
2015 | 3 | 02/16/2015 | 03/15/2015 | 02/16/2015 |
2015 | 3 | 02/16/2015 | 03/15/2015 | 02/23/2015 |
HeadCount Report for 2015:
Period | Beginning HeadCount |
1 | 1 |
2 | 3 |
3 | 3 |
HeadCount for each period is calculated as CountOfAssignments(AssignmentStartDate< PeriodStartDate
AND
(AssignmentEndDate is null or AssignmentEndDate >PeriodEndDate))
Thank you
Just remove the relationship and make period unique.
Hello @v-chuncz-msft here is the simplified data model.
Sample DataModel for headcount report
AssignmentTable
Id | AssignmentStartDate | AssignmentEnddate |
1 | 12/17/2014 | 02/15/2015 |
2 | 01/05/2015 | null |
3 | 01/12/2015 | 04/30/2015 |
4 | 01/21/2015 | 04/23/2015 |
5 | 02/16/2015 | null |
6 | 02/23/2015 | 07/31/2015 |
Period Table
Year | Period | PeriodStartDate | PeriodEndDate | Date |
2014 | 13 | 11/24/2014 | 12/21/2014 | 12/17/2014 |
2015 | 1 | 12/22/2014 | 01/18/2015 | 01/05/2015 |
2015 | 1 | 12/22/2015 | 01/18/2015 | 01/12/2015 |
2015 | 2 | 01/19/2015 | 02/15/2015 | 01/21/2015 |
2015 | 3 | 02/16/2015 | 03/15/2015 | 02/16/2015 |
2015 | 3 | 02/16/2015 | 03/15/2015 | 02/23/2015 |
HeadCount Report for 2015:
Period | Beginning HeadCount |
1 | 1 |
2 | 3 |
3 | 3 |
HeadCount for each period is calculated as CountOfAssignments(AssignmentStartDate< PeriodStartDate
AND
(AssignmentEndDate is null or AssignmentEndDate >=PeriodEndDate))
First off, there are several people that are on the Mt. Rushmore of DAX. One (technically 2) of these people is The Italians. They have a website called DAX patterns that addresses a lot of common issues such as this one. I think you can use this technique to help you:
You would have to manipulate it a bit, though. But essentially, you should be able to create two joins between the period table and fact table and create a measure that essentially looks like this:
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |