Infinity error

[Anonymous]

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 and still, it's not working. Below is the screenshot if the same:

ATT Termination is the numerator and Emp ID is the denominator.

Any help on this would be really appreciated!! Thanks in advance!!

Regards,

Mahesh

[Anonymous]

Hi  @Anonymous ,

Use DIVIDE for division in calculation. This will remove 'Infinity' label.

You can try to change the function to the following form:

Attrition Rate_Hide Future Date =
Var _lastdate= LASTDATE('Calendar'[Date])
IF(MAX('Calendar'[Date])= _lastdate, 
Divide(
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]))),0)

If it does not meet your expected results, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @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!!

[Anonymous]

@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)

[amitchandak]

@Anonymous , can share formula,

If you are dividing then use divide function

example

divide(sum(Table[A]),sum(Table[B]))

[Anonymous]

Hi @amitchandak ,

Below is the DAX I'm using at present in my report:

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])))

I cannot use DIVIDE in this situation as I need to filter out the NUMERATOR based on the dates.

Regards,
Mahesh

[amitchandak · Accepted solution]

@Anonymous ,if Attrition  rate is proble, refer my blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

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])))

)

[Anonymous]

@amitchandak , I've tried using DIVIDE but now I do not see any value for OCT-20 in my report. It's just giving blank

[amitchandak]

@Anonymous , first of all, check why are you getting oct -21 head count 0. I see that as issue.

In divide you can return 0 for infinity case

divide(a,b,0)