Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
@Maheshguptaz ,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])))
)
Hi @Maheshguptaz ,
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.
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:
@Maheshguptaz , can share formula,
If you are dividing then use divide function
example
divide(sum(Table[A]),sum(Table[B]))
Hi @amitchandak ,
Below is the DAX I'm using at present in my report:
@Maheshguptaz ,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])))
)
@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
@Maheshguptaz , 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)
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |