Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I am trying to create a measure that calculates the total count of hires within a specified date range, but does not include hires on the minimum date. I realize this might be a strange requirement, but it is what the user wants.
Right now, I am using this measure:
## - Hire Count (Regular) = CALCULATE( COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]), or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"), 'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular", 'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1, 'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0, FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) && 'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]>minx('Date','Date'[Date])) ) |
Solved! Go to Solution.
@garynorcrossmmc , Try with this small change
## - Hire Count (Regular) =
CALCULATE(
COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]),
or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"),
'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular",
'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1,
'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0,
FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) &&
'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]>Min('Date'[Date]))
)
/// Do you have to date , then try like
## - Hire Count (Regular) =
CALCULATE(
COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]),
or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"),
'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular",
'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1,
'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0,
FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) &&
'HR FACT_WORKFORCEEVENT'[EFFToDATE]>max('Date'[Date]))
)
Hi, @garynorcrossmmc
Has amitchandak’s reply helped you to successfully solve this problem?
If so, would you like to mark your own reply as a solution so that others can learn from it too?
Another important point you may dismiss is that you should make sure the date table doesn’t have a relationship with the main table, it may cause problems if you used the ‘Date’[Date] as the column in the slicer.
Thanks in advance!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@garynorcrossmmc , Try with this small change
## - Hire Count (Regular) =
CALCULATE(
COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]),
or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"),
'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular",
'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1,
'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0,
FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) &&
'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]>Min('Date'[Date]))
)
/// Do you have to date , then try like
## - Hire Count (Regular) =
CALCULATE(
COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]),
or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"),
'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular",
'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1,
'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0,
FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) &&
'HR FACT_WORKFORCEEVENT'[EFFToDATE]>max('Date'[Date]))
)