Date Calculations for Last Entry Date

[Anonymous]

Hi All, Good day. I hope someone can help me with this scenario. I need to get the date difference in the column "Signoff date". Calculate only the date difference if the last user name is Change Analyst - Manila from its last signoff date. As you can see below sample, the last date is Mar 27 (last exit) which is changed by Change Analyst - Manila, so I need to get the date difference from the last entry date, which is Mar 22. If the last change is not by Change Analyst - Manila, no need for calculating the date difference.

[Shishir22 · Accepted solution]

Hello @Anonymous ,

Please try below calculated column-

Column =
VAR _MAxdate =
    CALCULATE (
        MAX ( Data[Signoff Date1] ),
        FILTER (
            Data,
            Data[Status] <> "Post Release Tasks"
                && Data[User Name] <> "Change Analyst - Manila [APAC - SSC]"
                && Data[Change Number] = EARLIER ( Data[Change Number] )
        )
    )
VAR _ManilaDate =
    CALCULATE (
        MAX ( Data[Signoff Date1] ),
        FILTER (
            Data,
            Data[User Name] = "Change Analyst - Manila [APAC - SSC]"
                && Data[Change Number] = EARLIER ( Data[Change Number] )
        )
    )
RETURN
    ABS ( DATEDIFF ( _ManilaDate, _MAxdate, DAY ) + 1 )

Please mark it as answer if it resolves your issue. Kudos are also appreciated.

[Shishir22]

Hello @Anonymous ,

Can you please send data in tabular format instead of picture?

Also please send expected output.

[Anonymous]

Hi Shishir, here's the tabular sample. 

Change Number	Status	Status Entry Date	User Name	User Role	User Add Date	Signoff Date	Signoff Date1	Signoff Duration	Expected Output
QCH00057911	Approve Change	06-Dec-2021 03:52:38 PM SGT	Maria	Approver	06-Dec-2021 03:52:38 PM SGT	07-Dec-2021 05:56:20 AM SGT	12/7/2021	0
QCH00057911	Approve Change	06-Dec-2021 03:52:38 PM SGT	Rebecca	Approver	06-Dec-2021 03:52:38 PM SGT	07-Dec-2021 07:11:11 AM SGT	12/7/2021	0
QCH00057911	Approve Change	06-Dec-2021 03:52:38 PM SGT	Antonio	Approver	06-Dec-2021 03:52:38 PM SGT	07-Dec-2021 08:07:46 AM SGT	12/7/2021	0
QCH00057911	Collaborate	10-Nov-2021 12:17:11 PM SGT	Change Analyst - Manila [APAC - SSC]	Acknowledger	23-Nov-2021 08:43:48 AM SGT	02-Dec-2021 03:19:10 PM SGT	12/2/2021	7	6
QCH00057911	Collaborate	01-Oct-2021 09:19:23 AM SGT	Antonio	Acknowledger	01-Oct-2021 09:19:23 AM SGT	02-Oct-2021 12:37:15 PM SGT	10/2/2021	0
QCH00057911	Collaborate	01-Oct-2021 09:19:23 AM SGT	Maria	Acknowledger	01-Oct-2021 09:19:23 AM SGT	07-Oct-2021 05:17:22 AM SGT	10/7/2021	3
QCH00057911	Collaborate	10-Nov-2021 12:17:11 PM SGT	Antonio	Acknowledger	10-Nov-2021 12:17:11 PM SGT	10-Nov-2021 12:26:59 PM SGT	11/10/2021	0
QCH00057911	Collaborate	10-Nov-2021 12:17:11 PM SGT	Maria	Acknowledger	10-Nov-2021 12:17:11 PM SGT	22-Nov-2021 11:09:04 AM SGT	11/22/2021	7
QCH00057911	Collaborate	10-Nov-2021 12:17:11 PM SGT	AU Training Group	Acknowledger	10-Nov-2021 12:17:11 PM SGT	25-Nov-2021 11:57:25 AM SGT	11/25/2021	10
QCH00057911	Collaborate	01-Oct-2021 09:19:23 AM SGT	AU Training Group	Acknowledger	01-Oct-2021 09:19:25 AM SGT			6
QCH00057911	Post Release Tasks	07-Dec-2021 09:57:37 AM SGT	SSC LMC Admins	Acknowledger	07-Dec-2021 09:57:41 AM SGT	07-Dec-2021 02:18:32 PM SGT	12/7/2021	0
QCH00064812	Collaborate	15-Mar-2022 03:50:29 PM SGT	AU Training Group	Acknowledger	15-Mar-2022 03:50:34 PM SGT	16-Mar-2022 04:19:47 PM SGT	3/16/2022	1	4
QCH00064812	Collaborate	15-Mar-2022 03:50:29 PM SGT	Paul	Approver	15-Mar-2022 03:50:29 PM SGT	16-Mar-2022 06:27:01 AM SGT	3/16/2022	0	4
QCH00064812	Collaborate	15-Mar-2022 03:50:29 PM SGT	Wai	Approver	15-Mar-2022 03:50:29 PM SGT	16-Mar-2022 06:28:03 AM SGT	3/16/2022	0	4
QCH00064812	Collaborate	15-Mar-2022 03:50:29 PM SGT	Mahesh	Approver	15-Mar-2022 03:50:29 PM SGT	16-Mar-2022 11:30:19 AM SGT	3/16/2022	0	4
QCH00064812	Collaborate	21-Mar-2022 04:35:32 PM SGT	Wai	Acknowledger	21-Mar-2022 04:35:32 PM SGT	22-Mar-2022 07:01:19 AM SGT	3/22/2022	0	4
QCH00064812	Collaborate	21-Mar-2022 04:35:32 PM SGT	Paul	Acknowledger	21-Mar-2022 04:35:32 PM SGT	22-Mar-2022 08:26:19 AM SGT	3/22/2022	0	4
QCH00064812	Collaborate	21-Mar-2022 04:35:32 PM SGT	Change Analyst - Manila [APAC - SSC]	Acknowledger	21-Mar-2022 04:35:32 PM SGT	27-Mar-2022 04:19:47 PM SGT	3/27/2022	0	4

Expected output is date difference of below highlighted dates: Every Status has date difference