DAX Calculation find max value until certain date per employee (many-to-many)

[armandprr92]

Hello guys, 

i have a problem that is driving me nuts and I am coming to you for help.

I have two tables:

• The first one details the working schedules per employee. It shows the schedule and the date it was submitted.

• The second one is an order table and it contains working orders, a date and the employee.

I would like to get two new columns in the second table specifying the employee schedule. I specify columns as I need to do further calculations and I need to visualize the date without the employee field displayed. Even though any idea is welcome.

Notice that both tables can have multiple times the same employee and the dates are not comparable!

Example: 

Any recommendations? Thank you so much

[Syk · Accepted solution]

We can do this in Power Query.
Starting with your 2 tables, go to your Order table and hit merge queries

Select your employee shift table to merge. Then select your keys (which will be Employee and date). Click Employee then hold ctrl and click date. It should put numbers next to your columns to show what order they're matching like this.

Hit okay and expand the shift table

[armandprr92 · Accepted solution]

as a dax column can also be solved this way:

testcol =

    MAXX(

        FILTER(

            Employee_Schedule,

            Employee_Schedule[Employee] = Orders[Employee] &&

            Orders[Date] >= Employee_Schedule[Date]

        ),

        Employee_Schedule[Schedule Start]

    )

[Syk · Accepted solution]

We can do this in Power Query.
Starting with your 2 tables, go to your Order table and hit merge queries

Select your employee shift table to merge. Then select your keys (which will be Employee and date). Click Employee then hold ctrl and click date. It should put numbers next to your columns to show what order they're matching like this.

Hit okay and expand the shift table

[armandprr92]

So far I've been able to find this thread but unsuccessfully implemented it as it doesn't filter by two conditions. Plus I would like to have it in columns rather than measures.

https://community.fabric.microsoft.com/t5/Desktop/Mapping-between-2-table-with-Many-to-many-relation...