Forum Discussion

wheelsshark's avatar
wheelsshark
Frequent Visitor
4 years ago

Help in DAX to find Turnover for HR data

Hi,

 

I am working on my first Power BI for HR analytics Metrics. I am successful with all the visualizations and other basic calculations. Little confused with calculating the turnover for financial year. 

1. I have 2 types of year  in DateTable(normal year and financial year -  from october to september) - all the other changes and calculations are based on normal year ut just for the Turnover I need to use the Financial year. I dont know how to do that.

 

Turnover = Employees left during the financial year / permanent employees at start of financial year 

 

Employees left during the financial year = CALCULATE(DISTINCTCOUNT(HRMetricsData[EmpId]), USERELATIONSHIP(HRMetricsData[Termination Date],DateTable[Date]),NOT(ISBLANK(HRMetricsData[Termination Date]))) - this calculates for normal year, how should I do for financial year?

 

permanent employees at start of financial year = CALCULATE(
DISTINCTCOUNT(HRMetricsData[EmpId]),
FILTER(HRMetricsData,HRMetricsData[Start Date]<=MAX(DateTable[Date]) && ISBLANK(HRMetricsData[Termination Date]))
) - this also gives the count of normal year, not the financial year.

Can you please help m with the correct calculation.

 

 

 

Need Help

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    I think both of those measures should work if you include them with the Fiscal Year column in a table / matrix or other visual.

    You might want to add an additional clause in the permanent employees measure to count employees where either the termination date is blank or is after the max date.

  • wheelsshark's avatar
    wheelsshark
    Frequent Visitor
    4 years ago
    TerminationAtFinancialYear = CALCULATE(DISTINCTCOUNT(HRMetricsData[Emp#]), USERELATIONSHIP(HRMetricsData[Termination Date],DateTable[Fiscal Year]),NOT(ISBLANK(HRMetricsData[Termination Date])))+0
     
    Error: USERRELATIONSHIP function can only use the two columns references participating in relationship