cancel
Showing results for
Did you mean:
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

## 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 )
)```

You can also refer to the appendix.

Regards,

Jimmy Tao

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

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

## 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 )
)```

You can also refer to the appendix.

Regards,

Jimmy Tao

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

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

Anonymous
Not applicable

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

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

Announcements

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

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

Find out where you can attend!

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