topic Re: DATEDIFF for repeated rows in DAX Commands and Tips

DATEDIFF for repeated rows

Anonymous — Sun, 16 Oct 2022 20:26:29 GMT

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

Date	Column 1
1.10.22 0:01	0
1.10.22 0:02	0
1.10.22 0:03	0
1.10.22 0:04	1
1.10.22 0:05	1
1.10.22 0:06	1
1.10.22 0:07	0
1.10.22 0:08	0
1.10.22 0:09	1
1.10.22 0:10	1
1.10.22 0:11	0
1.10.22 0:12	0
1.10.22 0:13	0
1.10.22 0:14	0

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

Start time	End time	Duration
1.10.22 0:04	1.10.22 0:06	3 min
1.10.22 0:09	1.10.22 0:10	2 min

I tried with DATEDIFF but nothing. Thank you in advance

Re: DATEDIFF for repeated rows

tamerj1 — Mon, 17 Oct 2022 07:13:21 GMT

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

Re: DATEDIFF for repeated rows

Anonymous — Mon, 17 Oct 2022 06:42:45 GMT

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

Re: DATEDIFF for repeated rows

tamerj1 — Mon, 17 Oct 2022 07:16:11 GMT

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

Re: DATEDIFF for repeated rows

Anonymous — Mon, 17 Oct 2022 08:32:48 GMT

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

Re: DATEDIFF for repeated rows

tamerj1 — Mon, 17 Oct 2022 09:37:26 GMT

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