Reply
Applicable88
Impactful Individual
Impactful Individual
Partially syndicated - Outbound

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

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

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Syndicated - Outbound

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 )

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Syndicated - Outbound

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 )
avatar user

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)