Hi,
I am working on the HR Interactive dashboard using the same data files that you've provided. ( Ref dashboard link: https://www.youtube.com/watch?v=rsx43g7TBBs&t=1466s )
While working on the Separations chart, I have encountered an issue with numbers. My bad hire number is much greater than the separation number.
How could it be possible. I tried many times checking in power query but don't understand the issue here.
In your chart, the overall separation count is 1649 where as mine is 599.And the bad hire number is 1179. Unable to understand why this huge difference in count.
Whenever I tried loading the data model, it says 22,129 rows loaded and 1057 error. Could this be the problem.
I have attached the excel file. Please help me solve this issue.
PS: I am using Excel 365.
Hi Haritha,
You can't sum the 'BadHires' field like that because you'll be double counting some Employee ID's. e.g. EmpID 55512 has a total of 2 for both separation and bad hire in 2016 because they were present in the data for multiple months. This is why my Separations measure is not a simple COUNT or SUM formula.
You also need to clean the errors in the query otherwise your dashboard will not reconcile to mine. You also need to change the type using locale for the HireDate column.
Mynda
Hi Mynda,
Thanks for your response. For Separations, I have created a new measure as you've explained in the dashboard.
For the remaining, I will do as you suggested. Can you help me understand, is it because of the date data type columns that I am getting the errors while loading the data.
Thanks
Haritha
The errors you're getting in Power Query are because the source files are formatted d/m/y and your regional settings are m/d/y. Power Query tries to import the data on the assumption that it is also m/d/y and when it gets to a date that doesn't make sense in this format e.g. 14th December, it returns an error.
Mynda
Hi Mynda,
I understand now why I was getting those errors. Changed my regional settings and now there are no errors after loading data.
Thanks for the detailed explanation.
You are so wonderful.
Thanks
Haritha
Glad I could help. The better way to resolve the date issue is to use Change type > Using Locale... as you had already done for the other date field. However, if you're happy to work in dd/mm/yyyy format, then that's ok too.
Ohh ok. Thank you.
