calculate difference in date/time based on multiple up and down time

[EZiamslow]

Hi,

What would be the best option to calculate tool down time between the earliest D=down and earliest U=Up time for each instance?

DownTime is the result that I'm trying to work it out. In Tool column, I have multiple tool IDs. My end goal is to find out average each tool down time for weekly, monthly, and quarterly.

Thank you for helping!

Tool	DateTime	Availability	DownTime
AAA	8/20/2022 23:08	D
AAA	8/20/2022 22:08	D
AAA	8/20/2022 18:45	U	1:33
AAA	8/20/2022 17:12	D
AAA	8/20/2022 14:08	U	18:57
AAA	8/19/2022 19:11	D
AAA	8/18/2022 21:06	U
AAA	8/18/2022 20:15	U	2:00
AAA	8/18/2022 19:18	D
AAA	8/18/2022 18:15	D

Regards,
Eddie

[ryan_mayu · Accepted solution]

@EZiamslow 

you can try this

Column =

VAR _last=maxx(FILTER('Table','Table'[DateTime]<EARLIER('Table'[DateTime])),'Table'[DateTime])

VAR _lasta=maxx(FILTER('Table','Table'[DateTime]=_last),'Table'[Availability])

VAR _last2=maxx(FILTER('Table','Table'[DateTime]<EARLIER('Table'[DateTime])&&'Table'[Availability]="U"),'Table'[DateTime])

VAR _lasttime=minx(FILTER('Table','Table'[DateTime]>_last2),'Table'[DateTime])

return if ('Table'[Availability]="U" && _lasta="U", blank(), if('Table'[Availability]="U" && ISBLANK(_lasttime),'Table'[DateTime]-max('Table'[DateTime]),if('Table'[Availability]="U",'Table'[DateTime]-_lasttime)))

[Anonymous · Accepted solution]

Hi @EZiamslow ,

Based on the description, after getting data, selecting transform before Loading.

Selecting custom column and adding a year column.

Date.Year([Date]))

Selecting custom column, add a month column.

Date.Month([Date])

Then, selecting the desired year, reducing the number of data.

Closing and applying.

Then, using the DAX formula provided above.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EZiamslow]

@Anonymous 

which DAX formula are you referring to?

[Anonymous]

Hi @EZiamslow ,

Reducing the number of rows through the power query editor before loading the data. Then, using the @ryan_mayu  provide Dax formula. It should help.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@EZiamslow 

you can try this

Column =

VAR _last=maxx(FILTER('Table','Table'[DateTime]<EARLIER('Table'[DateTime])),'Table'[DateTime])

VAR _lasta=maxx(FILTER('Table','Table'[DateTime]=_last),'Table'[Availability])

VAR _last2=maxx(FILTER('Table','Table'[DateTime]<EARLIER('Table'[DateTime])&&'Table'[Availability]="U"),'Table'[DateTime])

VAR _lasttime=minx(FILTER('Table','Table'[DateTime]>_last2),'Table'[DateTime])

return if ('Table'[Availability]="U" && _lasta="U", blank(), if('Table'[Availability]="U" && ISBLANK(_lasttime),'Table'[DateTime]-max('Table'[DateTime]),if('Table'[Availability]="U",'Table'[DateTime]-_lasttime)))

[EZiamslow]

@ryan_mayu 

I have 600k rows and it crashed my Desktop app. Is there other options to make it work?

[ryan_mayu]

so far I don't have another better solution for this. Let's see if anyone else can help on this.