calculate the duration between row and the previous entry of the same work category.

[Applicable88]

Hello,

I need a calculation for every row, which displays the time difference from the entry before until that row. It also needs to be 

calculated for each category itself.

Here is a sample table:

Category		Timestamp		Hours differece
Work A		17.06.2022 13:30:00		48
Work A		15.06.2022 13:30:00		24
Work A		14.06.2022 13:30:00		72
Work A		11.06.2022 13:30:00		0
Work B		15.06.2022 13:30:00		24
Work B		14.06.2022 13:30:00		72
Work B		11.06.2022 13:30:00		0
Work C		17.06.2022 13:30:00		120
WorkC		12.06.2022 13:30:00		0

For instance the top entry is Work A which happened on June 17th 13.30PM. The entry before of that category would be from

June 15th also at 13:30PM. Which means the difference is 24 hours. I need that check for every entry. 

Thank you very much in advance.

Best

[tamerj1 · Accepted solution]

Hi @Applicable88 

you can create a new column 

Hours differece =
VAR CureentTimeStamp = TableName[Timestamp]
VAR CurrentCategoryTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Category] ) )
VAR PreviousTimeStampsTable =
    FILTER ( CurrentCategoryTable, TableName[Timestamp] < CureentTimeStamp )
VAR PreviousTimeStamp =
    COALESCE (
        MAXX ( PreviousTimeStampsTable, TableName[Timestamp] ),
        CureentTimeStamp
    )
RETURN
    DATEDIFF ( PreviousTimeStamp, CureentTimeStamp, HOUR )