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

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

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

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

    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.