Forum Discussion
Splitting time duration between over months
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
Hi
I'm still looking for a solution to this.
Does anyone have any ideas?
It would be much appreciatedThanks
3 Replies
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
)
)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 dayExamples belowThe 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:38so there should be a 4th row with a duration of 487 mins with a DailyOutageStart of 02/08/2024 17:53The first row duration should be 938 with a DailyOutageEnd of 05/08/2024 15:38Service 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
Hi
I'm still looking for a solution to this.
Does anyone have any ideas?
It would be much appreciatedThanks
