Forum Discussion
Splitting time duration between over months
Hi
I'm still looking for a solution to this.
Does anyone have any ideas?
It would be much appreciatedThanks
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