Subtract Same Row Dates

[NPC]

Hello,

I need to be able to subtract dates from the same row.

- Specifically, I care only about the date differnce btween the Statuses "SFT" and "REA" and only if they follow each other.

- If "SFT" is nt follwed by "REA", then I don't need the calculation. I deally, I would like for those to be left blank. 

- For example, for Equip_Num 367584264, I would like to calculate the Date_Difference between 5/17/2022 and 5/24/2022.

Here is what the table looks like:

Equip_Num	Status	Completed On	Date_Difference
367584264	SFT	5/1/2022
367584264	RVW	5/13/2022
367584264	SFT	5/17/2022
367584264	REA	5/24/2022
367584265	RVW	5/11/2022
367584265	SFT	5/15/2022
367584265	REA	5/20/2022
367584266	RVW	5/2/2022
367584266	SFT	5/3/2022

Thank you!

[NPC]

Thank you, this works!

[v-zhangti · Accepted solution]

Hi, @NPC 

You can try the following methods.

Column:

Previous Date =
MAXX (
    FILTER (
        'Table',
        [Completed On] < EARLIER ( 'Table'[Completed On] )
            && [Equip_Num] = EARLIER ( 'Table'[Equip_Num] )
    ),
    [Completed On]
)

Previous Status =
CALCULATE (
    MAX ( 'Table'[Status] ),
    FILTER (
        'Table',
        [Equip_Num] = EARLIER ( 'Table'[Equip_Num] )
            && [Completed On] = EARLIER ( 'Table'[Previous Date] )
    )
)

Follow = IF([Status]="REA"&&[Previous Status]="SFT",1,BLANK())

Date_Difference = IF([Follow]=1,[Completed On]-[Previous Date],BLANK())

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.