Use filter to show total maintenance time in an interval based on Start/End columns.

[DDL]

Hello. I have a dashboard that I currently do on Excel which I am trying to translate into Power BI, to take advantage of its dynamic filtering features.

Edit: Here is also a link to a sample of the table: truck_table.xlsx

The table shows a series of truck maintenance orders, with the date/hour of start and end of each maintenance, the truck number, and a few other data that are not being shown. In the spreadsheet, there is a cell where I pick a month and a year (i. e. June 2023). When I do that, the last column of the table, Month Time, calculates the total time of maintenance that happened in that month, for each order. For example, on line 632, the truck was in maintenance for 20:13 hours, however, only 16:49 of those hours happened in June, the rest was in May. I then use that column to generate a series of monthly KPIs.

I have created a Query to bring this table into BI, and now I want is to create graphs that show maintenance times for each truck (as well as other calculations involving maintenance), using filters that let me pick which days/months/etc I want to show. For example, if I pick June in a filter, I want the BI graph to give me only the total amount of maintenance that happened in June, which is basically the sum of that yellow column from the image. If I switch to May, I want me to show the May total. If I pick a day, I want it to show me the day total. And so on.

The problem is figuring out how to make Power BI understand that I want not to simply filter which lines should be included into the graph, but calculate on the total amount of time between the two columns on the table, relative to the filter. This is doable on Excel since it's basically a series of IF functions that add/substract the maintenance start/end times and the start/end of the month, but I don't know how to do it on PBI. I think a measure is probably how it is done, but I'm pretty new at Power BI so I don't know enough DAX to figure that out. Please help.

[DDL · Accepted solution]

Ok I've figured it out myself. The answer is sumx. Took me a while to learn this formula existed.

I made a new table with a calendar column, then used sumx to add the total maintenance time for each day. Now I can just create more columns to filter the specific trucks, and filter the graphs by day or month.

My thanks to anyone who's read this and tried to help figure it out, although I ended up doing it myself lol.

[mlsx4]

I have found an approach but it returns a complete hour value:

1) First you should do some transformations in Power Query (Start hour and Finish Hour should be format hour, create two new customize columns by joining StartDate&StartHour)

2) Once you have the table, create a calculated column by doing:

TimeDiff= DATEDIFF('Sample'[Start],'Sample'[End],HOUR)

*Take into account that Start and End are the two new columns

And then:

Truck in maintenance = 

var _lastDate= MAX('Tabla de fechas'[Fechas])
var _firstDate = MIN('Tabla de fechas'[Fechas])

var result= CALCULATE(SUM(TimeDiff),'Sample'[StartDate]<=_lastDate,'Sample'[EndDate]>=_firstDate||ISBLANK('Sample'[EndDate]))

return result

The problem is that it doesn't return a hh:mm:ss format, just an integer in hours (or minutes/second if you put it in the TimeDiff). But at least, it is a start point

[DDL]

Hello. Thank you for replying.

I did what you suggested and created the column. I'm assuming your Tabla de Fechas is a calendar table. I used mine, which goes from Jan 23 to Dec 23.

I've also managed to solve your issue of hours vs dates by creating the duration column inside the query, not in Dax. Either way, I also did your method, so I have one column showing the value in days and another one in hours. Then I used your formula to create the Truck In Maitenance column.

But... now what? Say I have this graph. How do I make a filter that shows only the maintenance time that happened in June, for example?

(my Power BI is in Portuguese, but I think the months in the axis are easy enough to understand, let me know if they are not).