Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate time difference based on Rank & Date time

Hi Guys,   I have a unique request to calculate the time difference between 2 orders -   Below is the sample data for your reference   Order  no Person name datetime Rank Time difference...
Need Help
Show and Tell
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Use the "EARLIER" function :

     

    Difference3 =
VAR currentindex_ = Sheet2[Index]
VAR t_date =
    CALCULATE (
        MAX ( Sheet2[datetime] ),
        FILTER (
            Sheet2,
            Sheet2[Index] = currentindex_ - 1
                && Sheet2[Rank] = 3
                && Sheet2[Person name]
                    = EARLIER ( Sheet2[Person name] )
        )
    )
RETURN
    DATEDIFF (
        t_date,
        Sheet2[datetime],
        SECOND
    )

     

     Also you can refer to this .pbix

     

    Best Regards,

    Liang 

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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi V-lianl-msft Thanks for this. However, I also need to calculate the difference between orders for resources as well.

i .e. difference between orders should be for individual resource. So my difference in orders should be separate for Daniel and Gabriel

 

Is this possible?

 

Regards,

Prathamesh Sable

V-lianl-msft's avatar
V-lianl-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

Use the "EARLIER" function :

 

Difference3 =
VAR currentindex_ = Sheet2[Index]
VAR t_date =
    CALCULATE (
        MAX ( Sheet2[datetime] ),
        FILTER (
            Sheet2,
            Sheet2[Index] = currentindex_ - 1
                && Sheet2[Rank] = 3
                && Sheet2[Person name]
                    = EARLIER ( Sheet2[Person name] )
        )
    )
RETURN
    DATEDIFF (
        t_date,
        Sheet2[datetime],
        SECOND
    )

 

 Also you can refer to this .pbix

 

Best Regards,

Liang 

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