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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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

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