cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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 .
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

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

@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 )
    )

Capture.PNG 

 

You can also refer to the appendix.

 

Regards,

Jimmy Tao

View solution in original post

Community Support Team
Community Support Team

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

@Anonymous,

 

You should create a calculate column, not a measure.

 

Regards,

Jimmy Tao

View solution in original post

5 REPLIES 5
Highlighted
Anonymous
Not applicable

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

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 .
Community Support Team
Community Support Team

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

@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 )
    )

Capture.PNG 

 

You can also refer to the appendix.

 

Regards,

Jimmy Tao

View solution in original post

Anonymous
Not applicable

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

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 ) ) '
Community Support Team
Community Support Team

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

@Anonymous,

 

You should create a calculate column, not a measure.

 

Regards,

Jimmy Tao

View solution in original post

Anonymous
Not applicable

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

@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 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,131)