Forum Discussion
Infinity error
Hi there, I've created a measure to calculate Turnover R12 in my data set. All the values are coming up correct except for a single month (Oct-20). I've tried to re-update the date in my dataset ...
Anonymous ,if Attrition rate is proble, refer my blog
If infinity error then use divide
divide(
IF(LASTDATE('Calendar'[Date]), CALCULATE(COUNTA('Termination Power BI'[Employee_ID]),
DATESBETWEEN('Calendar'[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(('Calendar'[Date])))),LASTDATE(('Calendar'[Date]))))
,
CALCULATE(COUNTA(ExcelDB_Headcount[Employee ID]), LASTDATE('Calendar'[Date]))))
VahidDM
Super User
Super UserHi Anonymous
What formula have you used in there? I think all ATT Termination % are wrong.
For instance, 401/17189 = 2.33% for Jan-2020
Appreciate your Kudos!!
VahidDM ,
Here's the DAX i used:
Attrition Rate_Hide Future Date =
IF(LASTDATE('Calendar'[Date]), CALCULATE(COUNTA('Termination Power BI'[Employee_ID]),
DATESBETWEEN('Calendar'[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(('Calendar'[Date])))),LASTDATE(('Calendar'[Date]))))
/
CALCULATE(COUNTA(ExcelDB_Headcount[Employee ID]), LASTDATE('Calendar'[Date])))
As suggested by amitchandak , I've modified it as below:
Attrition Rate_Hide Future Date = DIVIDE(
IF(LASTDATE('Calendar'[Date]), CALCULATE(COUNTA('Termination Power BI'[Employee_ID]),
DATESBETWEEN('Calendar'[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(('Calendar'[Date])))),LASTDATE(('Calendar'[Date])))),
CALCULATE(COUNTA(ExcelDB_Headcount[Employee ID]), LASTDATE('Calendar'[Date])))
and now I do not see any value for OCT-20 (this was giving infinity in the first place)
and now I do not see any value for OCT-20 (this was giving infinity in the first place)