returning value if date exists in range for specific names

[jshinnenkamp]

I have two tables, one with tasks which displays tasks and when they were copmleted along with the worker(employeeTasks), and other that lists workers and departments (workersInfo). I want to return the department of the worker for when the task was completed. Each table has a ID number. The issue is that there are employees that have switched departments, so the workersinfo table does not have distinct values.

Within the workersInfo table there are columns ValidFrom and ValidTo which are dates from when the employee started and finished with a certain department. The employeeTasks table also has dates to when the task was completed. So I want to return the departement of the employee when the task was completed.

WkStart is the date the task was completed and empNum is the ID number that is equal to the personnel number in Workers Info                      ValidFrom and ValidTo display when a worker was active within a specific department. I forgot to include personnel number but it is there.

Return department for the worker when WkStart falls between ValidFrom and ValidTo

[Ashish_Mathur]

Hi,

Share the link from where i can download the file and show the expected result.

[jshinnenkamp]

I'm unable to share the link due to working through Visual Studio SSAS.

[v-yuezhe-msft]

@jshinnenkamp,

Do you use ID field mentioned above to create relationship between the two tables? If so, please show us the sample data of ID field in the second table.

And could you please export data to Excel file and share us the Excel file vis OneDrive? Also please post expected result in table format based on above sample data so that we can provide you appropriate DAX. 

Regards,
Lydia

[jshinnenkamp]

I have a third table which is a list of employees so that is how the relationships are created.

Expected Result:

EmployeeTasks Table ->       Task                                  WkStart             WorkerName                    Department

                                              Company Meeting           1/14/17              Amanda                            Design

                                              Specail Event                    6/1/17                Amanda                            Design

                                              Company Meeting           10/14/17            Amanda                            HR

                                              Development                   11/1/17              Russ                                  IT

So in the employee task table I want the inserted column of department that checks which department the worker was in when the task was completed. In the example I have shown Amanda did her first tasks as part of the design team but she switched departments beofre her third task. The workersInfo table date range ValidFrom-ValidTo is how to check whichc department is active at the time of task completion.

[v-yuezhe-msft]

@jshinnenkamp,

Create the following measures in Employee table and create table visual as below,  then check if you get expected result.

maxWkstart = MAX(EmployeeTasks[WkStart])
Minfrom = MIN(WorkerInfo[ValidFrom])
maxto = MAX(WorkerInfo[ValidTo])
Department for worker = IF(AND([maxWkstart]<=[maxto], [maxWkstart]>=[Minfrom]),FIRSTNONBLANK(WorkerInfo[Department],""),BLANK())


Regards,
Lydia

[jshinnenkamp]

I would prefer the result to be a calculated column in the employeeTasks table so I can make visuals that tally amount of tasks each worker does. The issue with this result is that it only takes an employees most recent result, if an employee changes departemtns the column should know when that occurs and also change.

[v-yuezhe-msft]

@jshinnenkamp,

Please post expected result based on the sample data you share with me. You can take an employee for example to display the expected result.

Regards,
Lydia

[v-yuezhe-msft]

@jshinnenkamp,

Could you please paste the data of three tables in Excel and share the Excel file to us? Make sure that the relationship field is included in the sample data you share.

Regards,
Lydia