Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 prop...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    It was a wrong solution.

    Please refer to attached file with the correct solution

     

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

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

 

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

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Hi Anonymous 
        It was a wrong solution.

        Please refer to attached file with the correct solution

         

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