Need help with DAX

[Anonymous]

Hello All,

I'm stuck with a query to find the date difference in my data set.

Contract_EmployeeName	Created_On	completedOn	Expected Created Date	Expected EndDate	Category
Emp1	8/7/2023 16:51	8/7/2023 16:52	8/7/2023 16:51	8/8/2023 13:51	HR Coordinator
Emp1	8/7/2023 16:58	8/8/2023 13:51	8/7/2023 16:51	8/8/2023 13:51	HR Coordinator
Emp1	8/7/2023 16:58	8/9/2023 0:53	8/8/2023 13:51	8/9/2023 0:53	Employee

I need to get the "Expected Created Date" for the "Employee" category from the "Expected enddate" of the "HR Coordinator" category (as highlighted in bold), but I'm unable to get the dax for the same.

here's the dax I'm using currently:

Expected Created Date =

CALCULATE(MIN('table'[Created_On]), ALLEXCEPT('table', 'table'[Contract_EmployeeName], table[Category]))

Can anyone help me write this out?

Thanks for the help in advance!!

[Anonymous · Accepted solution]

Hi @Anonymous 

You can create the following measures

1.Rank

Rank = RANKX(FILTER(ALLSELECTED('Table'),[Contract_EmployeeName] in VALUES('Table'[Contract_EmployeeName])),CALCULATE(MAX([completedOn])),,ASC,Dense)

2.Expected Create Date

Expected Create Date =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Contract_EmployeeName]
                IN VALUES ( 'Table'[Contract_EmployeeName] )
                    && [Rank] = 1
        ),
        [Category]
    )
VAR b =
    CALCULATE ( [Rank] )
VAR c =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Contract_EmployeeName]
                IN VALUES ( 'Table'[Contract_EmployeeName] )
                    && [Category] <> SELECTEDVALUE ( 'Table'[Category] )
                    && [Rank] < b
        ),
        [Rank]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Category] ) = a,
        MAXX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Contract_EmployeeName]
                    IN VALUES ( 'Table'[Contract_EmployeeName] )
                        && [Rank] = 1
            ),
            [Created_On]
        ),
        CALCULATE (
            MAX ( 'Table'[completedOn] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Contract_EmployeeName]
                    IN VALUES ( 'Table'[Contract_EmployeeName] )
                        && [Rank] = c
            )
        )
    )

3.Expected End Date

Expected End Date = MAXX(FILTER(ALLSELECTED('Table'),[Contract_EmployeeName] in VALUES('Table'[Contract_EmployeeName])&&[Category] in VALUES('Table'[Category])),[completedOn])

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.