Subtract previous Date from current date to get the completion day for a task based on index

[Anonymous]

Hi Team, I have a table called 'Table1' which contains the following Requisition Id Employee ID Employee Name Requisition Date Approval Date/time Index Task Completion Days XYZ123 99991 Mark Nov 15, 2018 Nov 15, 2018 1 ------ XYZ123 99992 John Nov 15, 2018 Nov 16, 2018 2 ------ XYZ123 99993 Mike Nov 15, 2018 Nov 16, 2018 3 ---- XYZ123 99994 Jack Nov 15, 2018 Dec 6, 2018 4 ---- XYZ123 99994 Jack Nov 15, 2018 Dec 6, 2018 5 ---- XYZ124 99996 Dave Nov 19, 2018 Dec 10, 2018 1 ---- XYZ123 99997 Dave Nov 19, 2018 Dec 10, 2018 2 ------ Index is based on requisition id ( there are two different requisition IDs). I have created the index and also sorted the approval date . I would need help in getting expression for a new column which provides 'number of days to complete the task' based on index value i.e. If Index is 1 then it should be (Requisition Date- Approval Date/time) and If Index is greater the 1 THEN (Previous Approval Date/Time- Current Approval Date/time) This will give me a new column which shows number of days to complete the task . We can call this column 'Task completion Days'. I would need help from our community to provide me the expression. Thanks in Advance .

[v-yuta-msft · Accepted solution]

@Anonymous,

Based on your description, to achieve your requirement, you can create a calculate column using DAX below:

Task completion Days = 
VAR previous_approval_date =
    CALCULATE (
        MAX ( Table1[Approval Date/time] ),
        FILTER (
            Table1,
            Table1[Index]
                = EARLIER ( Table1[Index] ) - 1
                && Table1[Requistion ID] = EARLIER ( Table1[Requistion ID] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        Table1[Index] = 1, DATEDIFF ( Table1[Approval Date/time], Table1[Requisition Date], DAY ),
        Table1[Index] > 1, DATEDIFF ( Table1[Approval Date/time], previous_approval_date, DAY )
    )

 

You can also refer to the appendix.

Regards,

Jimmy Tao

[Anonymous]

Hi Team, I have a table called 'Table1' which contains the following Requisition Id, Employee ID, Employee Name, Requisition Date, Approval Date/time, Index, Task Completion Days ....Data( XYZ123, 99991, Mark , Nov 15, 2018, Nov 15, 2018, 1, ------ XYZ123, 99992, John, Nov 15, 2018 , Nov 16, 2018, 2, ------ XYZ123, 99993, Mike, Nov 15, 2018, Nov 16, 2018, 3, ---- XYZ123, 99994 , Jack, Nov 15, 2018, Dec 6, 2018, 4, --- XYZ123, 99994, Jack , Nov 15, 2018, Dec 6, 201, 5, ---- XYZ124, 99996, Dave, Nov 19, 2018, Dec 10, 2018, 1, ---- XYZ123, 99997, Dave Nov 19, 2018, Dec 10, 2018 , 2 , ------ Index is based on requisition id ( there are two different requisition IDs). I have created the index and also sorted the approval date . I would need help in getting expression for a new column which provides 'number of days to complete the task' based on index value i.e. If Index is 1 then it should be (Requisition Date- Approval Date/time) and If Index is greater the 1 THEN (Previous Approval Date/Time- Current Approval Date/time) This will give me a new column which shows number of days to complete the task . We can call this column 'Task completion Days'. I would need help from our community to provide me the expression. Thanks in Advance .

[v-yuta-msft · Accepted solution]

@Anonymous,

Based on your description, to achieve your requirement, you can create a calculate column using DAX below:

Task completion Days = 
VAR previous_approval_date =
    CALCULATE (
        MAX ( Table1[Approval Date/time] ),
        FILTER (
            Table1,
            Table1[Index]
                = EARLIER ( Table1[Index] ) - 1
                && Table1[Requistion ID] = EARLIER ( Table1[Requistion ID] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        Table1[Index] = 1, DATEDIFF ( Table1[Approval Date/time], Table1[Requisition Date], DAY ),
        Table1[Index] > 1, DATEDIFF ( Table1[Approval Date/time], previous_approval_date, DAY )
    )

 

You can also refer to the appendix.

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft 

Hello Jimmy,

Thanks for your expression . I  also need to find the latest date based on Index  ( i.e. Max(Index)) in Our case Index 4 is the highest index for requistion ID xyz123. I need to get the Latest Approval Date/Time( i.e. DEC 6,2018) AND SUBTRACT it with first requistion date ( Nov 15,2018) to get the number of days it took to complete the given task for a specified requistion ID . I will appreciate if you could help me getting the expression. 

Thanks 

[Anonymous]

Hi Jimmy, Thanks for your suggestion but when I am running this query , I am getting the following errror 'EARLIER/EARLIEST refers to an earlier row context which doesn't exist.'' AR previous_approval_date = CALCULATE ( MAX ( Sheet1[Index.End_Date]), FILTER ( Sheet1, Sheet1[Index.Index] = EARLIER( Sheet1[Index.Index] ) -1 && Sheet1[Index.Requisition_ID ] = EARLIER (Sheet1[Index.Requisition_ID] ) ) ) RETURN SWITCH ( TRUE (), Shee1[Index] = 1, DATEDIFF ( Table1[Approval Date/time], Table1[Requisition Date], DAY ), Sheet1[Index] > 1, DATEDIFF ( Table1[Approval Date/time], previous_approval_date, DAY ) ) '

[v-yuta-msft · Accepted solution]

@Anonymous,

You should create a calculate column, not a measure.

Regards,

Jimmy Tao