Splitting time duration between over months

[IAShakir]

Hi

I'm still a novice when it comes to writing DAX queries so I'm hoping this is an easy resolve for someone with more experience.

I have a table that is used to show the % uptime of a service over a time period (monthly) as part of a wider dashboard.

I've been using the outage duration column to calculate the % uptime for a certain date period. The time period is usually 1st of the month to last day of the month

This is fine until the outage spans overs 2 months

Index	Service Name	Outage start GMT	Outage end GMT	Outage.Days	Outage.Hours	Outage.Minutes	Outage duration (mins)
1	Remote Access - VPN	28/02/2025 00:00	05/03/2025 00:00	5	0	0	7200
2	Remote Access - VPN	29/07/2023 08:30	03/08/2023 10:15	4	1	45	10185
3	Remote Access - VPN	10/06/2021 08:30	10/06/2021 10:30	0	2	0	120
4	Remote Access - VPN	08/06/2021 18:00	08/06/2021 19:30	0	1	30	90
5	Remote Access - VPN	10/03/2020 05:00	11/03/2020 06:00	1	1	0	1500

So in the table above, the first 2 outages span over several days as well as overlapping months.

How would I split the outage duration to show the correct minutes for each day? 

For Index 1, there should be 1440 mins for each day

For Index 2, there should be 930 mins for 29/07 then 1440 mins for all other days then 615 mins for 03/08

Ideally, I'd like to split the whole row to show something similar to this (Index 2 as the example), where it shows the outage duration for each day of that index...

Index	Service Name	Outage start GMT	Outage end GMT	Outage.Days	Outage.Hours	Outage.Minutes	Outage duration (mins)
2	Remote Access - VPN	29/07/2023 08:30	30/07/2023 00:00	0	15	30	930
2	Remote Access - VPN	30/07/2023 00:00	31/07/2023 00:00	1	0	0	1440
2	Remote Access - VPN	31/07/2023 00:00	01/08/2023 00:00	1	0	0	1440
2	Remote Access - VPN	01/08/2023 00:00	02/08/2023 00:00	1	0	0	1440
2	Remote Access - VPN	02/08/2023 00:00	03/08/2023 10:15	0	10	15	615

Hope that makes sense

Any help is very much appreciated

Kind regards

Imran

[IAShakir · Accepted solution]

Hi
I'm still looking for a solution to this.
Does anyone have any ideas?

It would be much appreciated

Thanks

[bhanu_gautam]

@IAShakir Ensure you have a date table in your model. If not, create one.

Use DAX to create a calculated table that splits the outage duration into daily segments.

dax
OutageDailyBreakdown =
VAR OutageTable = 'YourOutageTable'
VAR DateTable = 'YourDateTable'
RETURN
ADDCOLUMNS(
FILTER(
CROSSJOIN(OutageTable, DateTable),
DateTable[Date] >= OutageTable[Outage start GMT] &&
DateTable[Date] <= OutageTable[Outage end GMT]
),
"DailyOutageStart",
IF(DateTable[Date] = OutageTable[Outage start GMT], OutageTable[Outage start GMT], DateTable[Date]),
"DailyOutageEnd",
IF(DateTable[Date] = OutageTable[Outage end GMT], OutageTable[Outage end GMT], DateTable[Date] + 1),
"DailyOutageMinutes",
DATEDIFF(
IF(DateTable[Date] = OutageTable[Outage start GMT], OutageTable[Outage start GMT], DateTable[Date]),
IF(DateTable[Date] = OutageTable[Outage end GMT], OutageTable[Outage end GMT], DateTable[Date] + 1),
MINUTE
)
)

[IAShakir]

@bhanu_gautam Thank you for getting back to me so quickly

I've managed to get the DAX working but the minutes for DailyOutageMinutes are not quite matching up.

Each row has been split into days but they all have the same 1440 minutes (24 hours)

The days also seem to have shifted by a day

Examples below

The times for 02/08/2024 - 03/08/2024 are missing but there is 05/08/2024 - 06/08/2024 even though the outage finished on 05/08/2024 15:38

so there should be a 4th row with a duration of 487 mins with a DailyOutageStart of 02/08/2024 17:53

The first row duration should be 938 with a DailyOutageEnd of 05/08/2024 15:38

Service Name	Outage start GMT	Outage end GMT	Outage.Days	Outage.Hours	Outage.Minutes	Outage duration (mins)	Date	DailyOutageStart	DailyOutageEnd	DailyOutageMinutes
Network LAN & Wireless	02/08/2024 17:53	05/08/2024 15:38	2	21	45	4185	05/08/2024 00:00	05/08/2024 00:00	06/08/2024 00:00	1440
Network LAN & Wireless	02/08/2024 17:53	05/08/2024 15:38	2	21	45	4185	04/08/2024 00:00	04/08/2024 00:00	05/08/2024 00:00	1440
Network LAN & Wireless	02/08/2024 17:53	05/08/2024 15:38	2	21	45	4185	03/08/2024 00:00	03/08/2024 00:00	04/08/2024 00:00	1440

Below was whole day outages so the duration is correct but the duration for 05/03/3035 is showing as 0 where it should also be 1440 (Outage was 6 days)

SERVICEREQID	Service Name	Outage start GMT	Outage end GMT	Outage.Days	Outage.Hours	Outage.Minutes	Outage duration (mins)	Date	DailyOutageStart	DailyOutageEnd	DailyOutageMinutes
IT205901	Remote Access - VPN	28/02/2025 00:00	05/03/2025 00:00	5	0	0	7200	05/03/2025 00:00	05/03/2025 00:00	05/03/2025 00:00	0
IT205901	Remote Access - VPN	28/02/2025 00:00	05/03/2025 00:00	5	0	0	7200	04/03/2025 00:00	04/03/2025 00:00	05/03/2025 00:00	1440
IT205901	Remote Access - VPN	28/02/2025 00:00	05/03/2025 00:00	5	0	0	7200	03/03/2025 00:00	03/03/2025 00:00	04/03/2025 00:00	1440
IT205901	Remote Access - VPN	28/02/2025 00:00	05/03/2025 00:00	5	0	0	7200	02/03/2025 00:00	02/03/2025 00:00	03/03/2025 00:00	1440
IT205901	Remote Access - VPN	28/02/2025 00:00	05/03/2025 00:00	5	0	0	7200	01/03/2025 00:00	01/03/2025 00:00	02/03/2025 00:00	1440
IT205901	Remote Access - VPN	28/02/2025 00:00	05/03/2025 00:00	5	0	0	7200	28/02/2025 00:00	28/02/2025 00:00	01/03/2025 00:00	1440

Apologies if I wasn't clear

Thanks in advance

Kind regards

Imran