Select rows using date slicer and pick only ones with highest priority and earliest start date

[Rafal_Duzowski]

Hi!

I need to create a report that shows how many employees were new to the company, new to position and new to departments within 6 months period from a selected date.

For example, when selecting 22-09-2021, selected period will be from 23-03-2021 until 22-09-2021.

The report should for each Department:

- calculate total headcount (all employees who worked in this department even for 1 day during the 6 months period

- calculate number of New to Company, New to Position and New to Department employees

- show a total of the above 3 "New" columns

- show a percentage of these employees over a total headcount

Additionally, after selecting a row, the report should display information about the employees who were "new" in any category.

Slicers: date and department

The report should look like on the below picture:

This would be pretty easy to achieve , but the business users requested for a few more selection criteria:

1. If employee is assigned to more than one category in the period of 6 months, select a row with the highest priority which order is is: New To Company (highest priority) , then New to Position, and last New to Department (lowest priority).

2. If employee happens to have more than one row in the category, the earliest assignment should be selected.

Example:

Employee 123 has the following assignments:

Start Date      Category

01-02-2021 - New to Company

01-04-2021 - New to Position

01-08-2021 - New to Department

01-09-2021 - New to Position

Within the selected period from 23-03-2021 until 22-09-2021, employee's assignment to display will be "01-04-2021 - New to Position" as New to Company is outside selected period, hence the highest priority is New To Position.

My data model is based on a factless fact table (FACT_EMPLOYMENT) which contains a single row for each date within employee's employment period, for example, if employee's employment period starts on 01-01-2021 and ends on 31-03-2021, FACT_EMPLOYMENT table will contain 90 rows.

For each row, there will be a key to assignments (used to determine if category is New to Company, Position , or Department), work location, position, job, etc. as many of these information has VALID_FROM and VALID_TO dates. 

The key here is a relationship between FACT_EMPLOYMENT and DIM_EMP_POS_ASSIGN (table containing all employee assignments). FACT_EMPLOYMENT is also linked to DIM_DATE which is used to limit the records for the date range.

Sample data: Excel spreadsheet 

Any ideas how to achieve it, preferably without using DAX? Tried several approaches using a second date table and RANKX but no success.

Can share more data if needed.

Thanks in advance!

[Greg_Deckler]

@Rafal_Duzowski Something like this will likely not be possible without DAX because it requires user interactivity. This likely means something like a DAX complex selector: The Complex Selector - Microsoft Power BI Community. However it is difficult to be certain without sample data and expected output. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.