Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need help with DAX

 

Hello All,

 

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

 

Contract_EmployeeNameCreated_OncompletedOnExpected Created DateExpected EndDateCategory
Emp18/7/2023 16:518/7/2023 16:528/7/2023 16:518/8/2023 13:51HR Coordinator
Emp18/7/2023 16:588/8/2023 13:518/7/2023 16:518/8/2023 13:51HR Coordinator
Emp18/7/2023 16:588/9/2023 0:538/8/2023 13:518/9/2023 0:53Employee

 

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!!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.