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 below table
Employee Number | Hired Date | Termination Date | Status |
332 | 25/10/1994 | Monday, 1 August 2016 | Terminated |
339 | 4/01/1995 | Monday, 1 August 2016 | Terminated |
604 | 18/08/1998 | Monday, 1 August 2016 | Terminated |
624 | 24/11/1998 | Monday, 24 April 2017 | Terminated |
660 | 17/08/1999 | Friday, 16 June 2017 | Terminated |
668 | 14/09/1999 | Monday, 6 August 2018 | Terminated |
778 | 26/11/2001 | Thursday, 13 September 2018 | Terminated |
792 | 13/02/2002 | Tuesday, 9 October 2018 | Terminated |
797 | 11/03/2002 | Monday, 5 November 2018 | Terminated |
818 | 4/09/2001 | Monday, 4 March 2019 | Terminated |
819 | 4/09/2001 | Monday, 27 May 2019 | Terminated |
831 | 29/07/2002 | Sunday, 16 January 2022 | Terminated |
853 | 5/09/2002 | Monday, 26 August 2019 | Terminated |
969 | 17/10/2003 | Active | |
970 | 29/10/2003 | Active | |
1004 | 21/04/2004 | Active | |
1019 | 20/08/2004 | Active | |
1039 | 27/09/2004 | Active | |
1083 | 13/08/2005 | Active | |
1093 | 25/08/2005 | Active | |
1148 | 26/09/2005 | Active | |
1157 | 4/11/2005 | Active | |
1186 | 21/08/2006 | Active |
I want to calculate Running total for Active Employees and Terminated Employees. I calculated total active and total termination employees using below dax measures
Total Active = CALCULATE(COUNT('Table'[Employee Number]),'Table'[Status] = "Active")
Total Termination = CALCULATE(COUNT('Table'[Status]),'Table'[Status] = "Terminated")
I created a Date Table and connected it with the Hire date in the table.
Then calculated running total for active employees as below
Active Employee RunningTotal = var MaxDate = MAX('Date'[Date])
RETURN
CALCULATE([Total Active],'Date'[Date] <= MaxDate,
ALL('Date'[Date]))
However, I am not able to calculate the running total for Terminated employees. Finally, I need to take the difference between active and running employees running total.
Can anyone advise how to do the above with two date columns in the table?
Solved! Go to Solution.
You need to create an inactive relationship between your calendar date and terminated date.
and improve the DAX measure as
Total Termination = CALCULATE(COUNT('Table'[Status]),'Table'[Status] = "Terminated",USERELATIONSHIP(Calendar[Date],Table[TerminationDate]))
Proud to be a Super User!
You need to create an inactive relationship between your calendar date and terminated date.
and improve the DAX measure as
Total Termination = CALCULATE(COUNT('Table'[Status]),'Table'[Status] = "Terminated",USERELATIONSHIP(Calendar[Date],Table[TerminationDate]))
Proud to be a Super User!
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 |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |