Forum Discussion
Helper IIIcalculate difference in date/time based on multiple up and down time
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
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)))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.
6 Replies
ryan_mayuSuper User
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)))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?
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.
