Forum Discussion
Comparing Hire Dates Against Date Table
Hello there. I am new to Power Bi and have been given a simple task for testing purposes at work. I have created a custom Date Table that contains every day between 2012-2018. I also have an EmployeeInfo table which contains the HireDate for each employee.
I need to search through the dates in the Date Table, if the Date Table date matches the HireDate in the EmployeeInfo table, I need to return the count of Employees for each year.
I tried to create an IF statement that was something like this: IF('EmployeeInfo'[HireDate] = 'Dates'[Date], COUNT(EmployInfo[EmployeeName]), 0) but I get an error whether I use a column or a measure, the error is "A single value for column 'Date' in table 'Dates' cannot be determined."
I want to use a bar graph to display the number of employees for each year. Any advice or pointers will be appreciated, Googling and testing has been taking a bit longer than I anticipated.
Thanks!
Hi selyza
Please have a look at my test.
Create calculated columns in the EmployeeInfo table
Column = YEAR([hire date]) Column 2 = CALCULATE(COUNT(employee[name]),FILTER(ALL(employee),[Column]=EARLIER(employee[Column])))
Then add "column2" to the Value field.
Note: relationship between two tables is needed.
More details please see my pbix.
Best Regards
Maggie
2 Replies
Hi selyza
Please have a look at my test.
Create calculated columns in the EmployeeInfo table
Column = YEAR([hire date]) Column 2 = CALCULATE(COUNT(employee[name]),FILTER(ALL(employee),[Column]=EARLIER(employee[Column])))
Then add "column2" to the Value field.
Note: relationship between two tables is needed.
More details please see my pbix.
Best Regards
Maggie
Hi there, thank you for your reply. I managed to get the project to work. I imported the Date Table as a data source since it wasn't showing up in the Query edititor. Was imported I was able to make a new query so I could make the graph I needed.
Thanks again!
