Forum Discussion

NPC's avatar
NPC
Helper I
4 years ago
Solved

Subtract Same Row Dates

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_NumStatusCompleted OnDate_Difference
367584264SFT5/1/2022 
367584264RVW5/13/2022 
367584264SFT5/17/2022 
367584264REA5/24/2022 
367584265RVW5/11/2022 
367584265SFT5/15/2022 
367584265REA5/20/2022 
367584266RVW5/2/2022 
367584266SFT5/3/2022 

 

Thank you!

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    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.