Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , can share formula,

 

If you are dividing then use divide function

 

example

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        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)