Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear All,
I am working in Employee headcount analysis.Headcount will be calculated from employee history values.
I am having Employehistory and Date table.Dateofjoining active relationship and DateofLeaving Inactive relationship.Also I am having effctivedate column.In report I have Date slicer (Between) when slicer selected (Max date on slicer) I need to pass and filter prior values from history . Department and Grade are counted on relavant effective date.Kindly help me how to filter.T-SQL i have achived this.I am new to DAX so struggling to get....
Emp_ID | Effective_From | Department | Designation | Grade | Join Date | Leaving Date | Leaving Status | Record No |
1 | 01-Jan-2016 | Product | System Engineer | 100 | 01-Jan-2016 | NULL | 0 | 1 |
1 | 02-Jan-2017 | Proudct | Sr.System Engineer | 110 | 01-Jan-2016 | NULL | 0 | 2 |
1 | 02-Mar-2018 | R&D | Asst.Manager | 210 | 01-Jan-2016 | NULL | 0 | 3 |
2 | 07-Jan-2015 | Support | System Engineer | 100 | 07-Jan-2015 | NULL | 0 | 1 |
2 | 04-Dec-2015 | Support | Sr.System Engineer | 110 | 07-Jan-2015 | NULL | 0 | 2 |
2 | 10-Oct-2016 | Implementation | Sr.System Engineer | 110 | 07-Jan-2015 | NULL | 0 | 3 |
2 | 25-Mar-2017 | Implementation | System Analyst | 120 | 07-Jan-2015 | NULL | 0 | 4 |
2 | 30-Nov-2017 | Delivery | System Analyst | 120 | 07-Jan-2015 | NULL | 0 | 5 |
2 | 01-Apr-2018 | Delivery | Sr.System Analyst | 130 | 07-Jan-2015 | NULL | 0 | 6 |
2 | 18-Jul-2018 | Delivery | Sr.System Analyst | 130 | 07-Jan-2015 | 18-Jul-2018 | 1 | 7 |
Solved! Go to Solution.
Hi @gsrk1982 ,
I'm not so sure for your requirement, can you please explain more about these?
If You mean calculate available employee count in selected date range, you can try to use following measure formula:
Measure = VAR _calendar = CALENDAR ( MINX ( VALUES ( Date[Date] ), [Date] ), MAXX ( VALUES ( Date[Date] ), [Date] ) ) RETURN CALCULATE ( COUNTROWS ( VALUES ( Table[Emp_ID] ) ), FILTER ( ALLSELECTED ( Tabele ), [Effective_from] IN _calendar ) )
Regards,
Xiaoxin Sheng
Hi @gsrk1982 ,
I'm not so sure for your requirement, can you please explain more about these?
If You mean calculate available employee count in selected date range, you can try to use following measure formula:
Measure = VAR _calendar = CALENDAR ( MINX ( VALUES ( Date[Date] ), [Date] ), MAXX ( VALUES ( Date[Date] ), [Date] ) ) RETURN CALCULATE ( COUNTROWS ( VALUES ( Table[Emp_ID] ) ), FILTER ( ALLSELECTED ( Tabele ), [Effective_from] IN _calendar ) )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |