Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

DATEDIFF for repeated rows

Hey everybody! I have an issue and would be appreciated for your help.

 

I have a table below that contains information about power station's working time. Column 1 shows us when station works properly (0) and when it shuts down (1):

DateColumn 1
1.10.22 0:010
1.10.22 0:020
1.10.22 0:030
1.10.22 0:041
1.10.22 0:051
1.10.22 0:061
1.10.22 0:070
1.10.22 0:080
1.10.22 0:091
1.10.22 0:101
1.10.22 0:110
1.10.22 0:120
1.10.22 0:130
1.10.22 0:140

 

I need to create report  that shows me how long station hasn't worked and when it happend, something like this:

Start timeEnd timeDuration
1.10.22 0:041.10.22 0:063 min
1.10.22 0:091.10.22 0:102 min

 

I tried with DATEDIFF but nothing. Thank you in advance

1 ACCEPTED SOLUTION

Hi @Anonymous 
It was a wrong solution.

Please refer to attached file with the correct solution

1.png2.png

 

Down Order = --column 
IF ( 
    'Nizhny Tagil#1'[HP GOX] = 1,
    RANKX ( 
        FILTER ( 'Nizhny Tagil#1', 'Nizhny Tagil#1'[HP GOX] = 1 ),
        VAR T1 = FILTER ( 'Nizhny Tagil#1', 'Nizhny Tagil#1'[Date] <= EARLIER ( 'Nizhny Tagil#1'[Date] ) )
        RETURN
            COUNTROWS (T1)
            - SUMX (
                T1,
                'Nizhny Tagil#1'[HP GOX]
            ),, 
            ASC, 
            Dense
    )
)
Start Time = --measure
IF ( 
    NOT ISBLANK ( SELECTEDVALUE ( 'Nizhny Tagil#1'[Down Order] ) ),
    MIN ( 'Nizhny Tagil#1'[Date] )
)
End Time = --measure
IF ( 
    NOT ISBLANK ( SELECTEDVALUE ( 'Nizhny Tagil#1'[Down Order] ) ),
    MAX ( 'Nizhny Tagil#1'[Date] )
)
Downtime = --measure
SUMX (
    VALUES ( 'Nizhny Tagil#1'[Down Order] ),
    IF (
        'Nizhny Tagil#1'[Down Order] <> BLANK (),
        DATEDIFF ( [Start Time], [End Time], MINUTE )
    )
)

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@tamerj1 Thank you for good example, maybe I do something wrong but this is what I got.

 

In calculated column everything looks fine in a first glance

Agedpie_0-1665988678495.png

 

But when it comes to visual, it doesn't work properly

Agedpie_1-1665988836310.png

 

Hi @Anonymous 
My Mistake, I put "DAY" instead of "MINUTE". I have updated the solution in my original reply. Also to see the duration of each down period you need to add the [Down Order] column in the table visual

Anonymous
Not applicable

@tamerj1 Still nothing=(

Could you please take a glance on .pbix, maybe I missed something

https://drive.google.com/file/d/1o6Nym95tV-ZGRDTBSbSxyJPV-2I0CQxh/view?usp=sharing 

Hi @Anonymous 
It was a wrong solution.

Please refer to attached file with the correct solution

1.png2.png

 

Down Order = --column 
IF ( 
    'Nizhny Tagil#1'[HP GOX] = 1,
    RANKX ( 
        FILTER ( 'Nizhny Tagil#1', 'Nizhny Tagil#1'[HP GOX] = 1 ),
        VAR T1 = FILTER ( 'Nizhny Tagil#1', 'Nizhny Tagil#1'[Date] <= EARLIER ( 'Nizhny Tagil#1'[Date] ) )
        RETURN
            COUNTROWS (T1)
            - SUMX (
                T1,
                'Nizhny Tagil#1'[HP GOX]
            ),, 
            ASC, 
            Dense
    )
)
Start Time = --measure
IF ( 
    NOT ISBLANK ( SELECTEDVALUE ( 'Nizhny Tagil#1'[Down Order] ) ),
    MIN ( 'Nizhny Tagil#1'[Date] )
)
End Time = --measure
IF ( 
    NOT ISBLANK ( SELECTEDVALUE ( 'Nizhny Tagil#1'[Down Order] ) ),
    MAX ( 'Nizhny Tagil#1'[Date] )
)
Downtime = --measure
SUMX (
    VALUES ( 'Nizhny Tagil#1'[Down Order] ),
    IF (
        'Nizhny Tagil#1'[Down Order] <> BLANK (),
        DATEDIFF ( [Start Time], [End Time], MINUTE )
    )
)

 

tamerj1
Super User
Super User

Hi @Anonymous 

you need to create a calculated column that sort the down occurances in order. Then you can place this column in the table visual followed by the following measures.

 

 

Down Order = --column 
IF (
    'Table'[Column1] = 1,
    SUMX (
        FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) ),
        'Table'[Column1]
    )
)
Start Time = --measure 
MIN ( 'Table'[Date] )
End Time = --measure 
MAX ( 'Table'[Date] )
Downtime = --measure 
SUMX (
    VALUES ( 'Table'[Down Order] ),
    IF (
        'Table'[Down Order] <> BLANK (),
        DATEDIFF ( [Satrt Time], [End Time], MINUTE)
    )
)

 

 

 

 

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.