Arrange values based on a date from a table AND on a data range from another table

[vyny17]

Hey there Power BI enthusiast and experts!

Theses days I have faced a quite complex issue on a dashboard I'm dealing with

Here it is:

There is a table called 'Workers' wich provides informations about the workers such as: Name, Role, Date of Admission and resignation

When a worker changes its function on the enterprise it appears in another row followed by de Date he first worked as the new role and the date of resignation (if it's empty the worker was not fired yet)

In that case 'Albert' changed role in 2/1/2021 and resigned in 31/2021 (DD/MM/YYYY)

In another table we have the informations given by the workers listing the days they worked

Here is the thing, i need to create the column 'Role' as it appears on the image above, this column will point in wich role the worker was on when he appointed his work.

Filling the rows mannually it has to seems like this:

If you could help me to find the DAX code needed to do that (CONSIDERING THAT THE RELATIONSHIP BETWEEN THE TABLES IS OVER THE NAME - MANY TO MANY)

I'd be really thankful!

[VahidDM]

Hi @vyny17 

Try this to add a new column with DAX:

Role = 
CALCULATE (
    MAX ( Workers[Role] ),
    FILTER (
        ALL ( Workers ),
        Workers[Admission Date] <= EARLIER ( 'Work Day'[Time entry created at] )
            && IF (
                ISBLANK ( Workers[Demission Date/Change in Contract] ),
                TRUE (),
                Workers[Demission Date/Change in Contract]
                    >= EARLIER ( 'Work Day'[Time entry created at] )
            )
            && 'Work Day'[Name] = EARLIER ( 'Work Day'[Name] )
    )
)

Output:

Sample file attached.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ryan_mayu · Accepted solution]

@vyny17 

pls try this

column = maxx(FILTER('Table','Table (2)'[Name]='Table'[Name]&&'Table (2)'[Time]>='Table'[Admission Date]&&('Table (2)'[Time]<='Table'[Demission Date]||ISBLANK('Table'[Demission Date]))),'Table'[Role])

pls see the attachment below

[vyny17]

Thanks, man!

Your solution worked and helped me a lot!

That is exactly what I was looking for!

[ryan_mayu]

you are welcome