Forum Discussion
Calculate time difference based on Rank & Date time
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.
Community SupportHi Anonymous ,
First, you can create an index column in "Edit queries" to get the previous "datetime" value.
And then use "DATEDIFF" function to calculate the time difference.
Difference =
VAR currentindex_ = Sheet2[Index]
RETURN
DATEDIFF (
CALCULATE (
MAX ( [datetime] ),
FILTER ( Sheet2, Sheet2[Index] = currentindex_ - 1 ),
FILTER ( Sheet2, Sheet2[Rank] = 3 )
),
[datetime],
SECOND
)Finally, how to convert seconds into standard time can you refer to this blog.
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.
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
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.