Sumarize per Month sliced downtime

[eduardomartins]

Hello there!

I'm trying hard to solve a problem and I'm getting nowhere here, so I need some help!

Here's the problem. I have a downtime table like this below:

The fisrt column (Data Inicio) is the beginning of the asset downtime, the second column (Data Fim) is the end of the asset downtime and the third column is the difference in minutes of the two first columns.

What I want to do it's quite simple (it should be): I want to sumarize by month the downtime, but I need to slice the downtime in each month between the beginning date and the the end date for all rows!!

Take for example the first row. We have 6 months between the beginning and the end of the downtime. What I want is:

But for all the rows of the table, of course. The first month and the last one it's easy to get, because I have the reference dates, but the middle ones I couldn't think in a way to do that (and I've already tried lots of ways).

Can anyone help me, please? 🙂

Thanks!

[fhill · Accepted solution]

This one was a bit crazy, so try to stick with me...  ** Make sure all Date / Time columns are formatted as such! **

First, I needed to create a boarder table I called 'Months'.  Since we are looking at Minutes instead of days, I had to take this down to the Time level where every month starts at 00:00:00 and every month ends at 23:59:59.  You can do this easily in excel or by hand (it won't take that long.)

Here is your data:

I needed a good way to apply If / Then logic to each of your  dates to see how they merge into the different months.  I did this by creating a new Custom Table (not in Query Editor) under the Modeling Tab of Power BI.  This CrossJoin command will mesh the two tables together for every row.

Now I can start throwing math against the problem.. here are my 4 custom columns to account for periods starting during a month, a full month, or periods ending during a month.  Then a Total for easy visuals:

PartialMonthStart = IF('Table'[StartMonth] <= 'Table'[Start] && 'Table'[EndMonth] >= 'Table'[Start],DATEDIFF('Table'[Start],'Table'[EndMonth],MINUTE) + 1 )

FullMonth = IF('Table'[StartMonth] >= 'Table'[Start] && 'Table'[EndMonth] <= 'Table'[End],DATEDIFF('Table'[StartMonth],'Table'[EndMonth],MINUTE) + 1)

PartialMonthEnd = IF('Table'[StartMonth] <= 'Table'[End] && 'Table'[EndMonth] >= 'Table'[End],DATEDIFF('Table'[StartMonth],'Table'[End],MINUTE) + 1 )

*** The +1 at the end is VERY important, b/c eadh day ends at 23:59:59, I have to manaully add in the missing full minute. ***

TotalMinutesDown = 'Table'[PartialMonthStart] + 'Table'[FullMonth] + 'Table'[PartialMonthEnd]

Now that you have mutple lines per Month, you can easily create a Visual SUM'ing the Total Minutes column.  See below where I have the raw data (with a calculated Durration Minutes, and then my chart with a matching SUM when looking at the values by month.

Thank You,

FOrrest

[eduardomartins]

Anyone? 😞

[fhill]

Does this have to be in Minutes?  If you want to look at Downtime Days, I might have some ideas?

FOrrest

[eduardomartins]

Hi there @fhill!

It could be in days, no problem. The logic would be the same, I think. 🙂

[fhill]

hahahahaha... MUCH Easier in Days!   But oh well, I already figured out a Minutes option...

[eduardomartins]

@fhill, Thank you very much, my friend! It worked as I need it!!! 😄