Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wheelsshark
Frequent Visitor

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.

wheelsshark_0-1650325593559.png

 

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.

 

 

 

2 REPLIES 2
wheelsshark
Frequent Visitor

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
johnt75
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors