Forum Discussion

IAShakir's avatar
IAShakir
Regular Visitor
1 year ago
Solved

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 o...
Need Help
  • IAShakir's avatar
    IAShakir
    1 year ago

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

    It would be much appreciated

     

    Thanks

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
IAShakir
Regular Visitor
1 year ago

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 NameOutage start GMTOutage end GMTOutage.DaysOutage.HoursOutage.MinutesOutage duration (mins)DateDailyOutageStartDailyOutageEndDailyOutageMinutes
Network LAN & Wireless  02/08/2024 17:53  05/08/2024 15:3822145418505/08/2024 00:0005/08/2024 00:0006/08/2024 00:001440
Network LAN & Wireless  02/08/2024 17:53  05/08/2024 15:3822145418504/08/2024 00:0004/08/2024 00:0005/08/2024 00:001440
Network LAN & Wireless  02/08/2024 17:53  05/08/2024 15:3822145418503/08/2024 00:0003/08/2024 00:0004/08/2024 00:001440
 
 
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)
 
SERVICEREQIDService NameOutage start GMTOutage end GMTOutage.DaysOutage.HoursOutage.MinutesOutage duration (mins)DateDailyOutageStartDailyOutageEndDailyOutageMinutes
IT205901Remote Access - VPN  28/02/2025 00:00  05/03/2025 00:00500720005/03/2025 00:0005/03/2025 00:0005/03/2025 00:000
IT205901Remote Access - VPN  28/02/2025 00:00  05/03/2025 00:00500720004/03/2025 00:0004/03/2025 00:0005/03/2025 00:001440
IT205901Remote Access - VPN  28/02/2025 00:00  05/03/2025 00:00500720003/03/2025 00:0003/03/2025 00:0004/03/2025 00:001440
IT205901Remote Access - VPN  28/02/2025 00:00  05/03/2025 00:00500720002/03/2025 00:0002/03/2025 00:0003/03/2025 00:001440
IT205901Remote Access - VPN  28/02/2025 00:00  05/03/2025 00:00500720001/03/2025 00:0001/03/2025 00:0002/03/2025 00:001440
IT205901Remote Access - VPN  28/02/2025 00:00  05/03/2025 00:00500720028/02/2025 00:0028/02/2025 00:0001/03/2025 00:001440

 

Apologies if I wasn't clear

 

Thanks in advance

 

Kind regards

Imran