Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys, i'm hoping one of you geniuses (Genii?) will be able to help with an query i need to construct...
I've got a table full of ticket requests, each with a predetermined SLA, that i need to use to calculate the required completion time and determine whether it was actioned in time or not.
For example, ID 1234 (SysAid Extract.[ID]) was logged at 08:57 on Monday as Urgent (SysAid Extract.[Urgency]). As 08:57 is before working hours then the start time should be 09:00 by default on the same day, and a 4-hour completion time applied based on the Urgent priority, giving a completion time of 13:00. Similarly, any records received after 17:00 hours should default to 09:00am the preceeding day.
I'm also considering adding a holding table for Bank Holidays (TblHolidays or similar) and any internal days off (conferences, stand-down days etc) that can be linked so these dates are also excluded from the days when calculating required completion time and dates.
I've noticed that the DimDate table also has an IsHoliday boolean column that can be referred to also exclude any holiday days when calculating required completion time and dates.
The final complexity to this would be that a required completion date and time (SysAid Extract.[Required Completion Date]) can be added to over-ride the priority completely.
I've attached a screenshot of the table model below for reference;
Hi @javirmerino ,
We can try to create a calculated column using following dax to meet your requirement:
Required Completion Date =
VAR d = [Date]
VAR t = [Time]
VAR isWorkDay =
CALCULATE (
COUNTROWS ( 'Dim_Date' ),
FILTER (
'Dim_Date',
[Date] = d
&& [isHoliday]
&& ( WEEKDAY ( [Date] ) IN { 1, 7 } )
)
) = 0
VAR beforeWorkTime =
t < TIME ( 9, 0, 0 )
VAR inWorkTime =
AND ( t >= TIME ( 9, 0, 0 ), t <= TIME ( 17, 0, 0 ) )
RETURN
SWITCH (
TRUE (),
isWorkDay && inWorkTime, d + t + 4 / 24,
isWorkDay && BeforeWorkTime, d + TIME ( 9, 0, 0 ) + 4 / 24,
CALCULATE (
MIN ( 'Dim_Date'[Date] ),
FILTER (
'Dim_Date',
[Date] > d
&& NOT ( [isHoliday] )
&& NOT ( WEEKDAY ( [Date] ) IN { 1, 7 } )
)
)
+ TIME ( 9, 0, 0 ) + 4 / 24
)
If it doesn't meet your requirement, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to One Drive and share the link here.
Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Thanks for the timely response, @v-lid-msft! That's almost sorted it, but the hours added needs to be dynamic based on the urgency level. I'm also having some issues with the [date] and [time] fields in your query showing as incorrect syntax. Could it be that i have a calculated field within my extract with these names also?
I've amended your query very slightly to represent the actual field names in my data source as below;
Measure = VAR d = [Date]
VAR t = [Time]
VAR isWorkDay =
CALCULATE (
COUNTROWS ( 'Dim_Date' ),
FILTER (
'Dim_Date',
[Date] = d
&& [IsHolidayUK]
&& ( WEEKDAY ( [Date] ) IN { 1, 7 } )
)
) = 0
VAR beforeWorkTime =
t < TIME ( 9, 0, 0 )
VAR inWorkTime =
AND ( t >= TIME ( 9, 0, 0 ), t <= TIME ( 17, 0, 0 ) )
RETURN
SWITCH (
TRUE (),
isWorkDay && inWorkTime, d + t + TblKPI.[hours] / 24,
isWorkDay && BeforeWorkTime, d + TIME ( 9, 0, 0 ) + 4 / 24,
CALCULATE (
MIN ( 'Dim_Date'[Date] ),
FILTER (
'Dim_Date',
[Date] > d
&& NOT ( [IsHolidayUK] )
&& NOT ( WEEKDAY ( [Date] ) IN { 1, 7 } )
)
)
+ TIME ( 9, 0, 0 ) + 4 / 24
)
i've also included an example extract of how i expect the data to be produced. See below;
ID | Type | Sub Type | Owner | Title | Description | Status | Due Now Status | Urgency | Received | Required Completion Date | WorkingDayRecieved | DueDate |
73971 | Maintenance | Other | Operations Support | Ticket Request Title | This contains string data around the query request | New | Missed | Urgent | 24/01/2020 14:05 | 24/01/2020 14:05 | 27/01/2020 10:05 | |
73992 | Maintenance | Other | Operations Support | Ticket Request Title | This contains string data around the query request | New | Due Now | Urgent | 27/01/2020 08:57 | 27/01/2020 09:00 | 27/01/2020 13:00 | |
73993 | Maintenance | Other | Operations Support | Ticket Request Title | This contains string data around the query request | New | Due Now | Urgent | 27/01/2020 08:59 | 27/01/2020 09:00 | 27/01/2020 13:00 | |
73987 | Maintenance | Other | Operations Support | Ticket Request Title | This contains string data around the query request | New | Due Now | Very High | 24/01/2020 17:47 | 27/01/2020 09:00 | 27/01/2020 17:00 | |
73940 | Maintenance | Other | Operations Support | Ticket Request Title | This contains string data around the query request | New | Due Now | High | 24/01/2020 09:56 | 24/01/2020 09:56 | 28/01/2020 09:56 | |
73948 | Maintenance | Other | Operations Support | Ticket Request Title | This contains string data around the query request | New | Due Now | High | 24/01/2020 11:18 | 24/01/2020 11:18 | 28/01/2020 11:18 | |
73927 | Maintenance | Other | Operations Support | Ticket Request Title | This contains string data around the query request | New | Due Now | Normal | 24/01/2020 07:58 | 24/01/2020 09:00 | 29/01/2020 09:00 | |
73873 | Maintenance | Other | Operations Support | Ticket Request Title | This contains string data around the query request | New | Due Now | Low | 22/01/2020 16:35 | 22/01/2020 16:35 | 29/01/2020 16:35 | |
73968 | Maintenance | Other | Operations Support | Ticket Request Title | This contains string data around the query request | New | Due Now | Low | 24/01/2020 13:45 | 27/01/2020 17:00 | 24/01/2020 13:45 | 27/01/2020 17:00 |
Received is the date/time of the received request, straight form the source with no manipulation.
WorkingDayRecieved is calculated based on the Received field, so it defaults to the start of the next working day (9am if raised before 9am, excludes weekends and bank holidays etc)
Required Completion Date when this field is populated with a date/time, this value should be the DueDate of the record. When empty, the due date should be calculated based on the WorkingDayRecieved field plus hours dtermined by the urgency of the request. As you'll see, the first 3 Urgent records have a 4 hour window for completion. The 4th record (ID 73987) is Very High so has been allocated an 8-hour completion window. Meanwhile ID 73873 is low priority so have 40 working hours to complete.
I hope that clarifies any ambiguities, but i'm here to answer anything else that needs clarity. Thanks again for responding so quickly.
A
Hi, I am not sure I have an answer to your question but was curious how you connected sysaid to Power Bi ?
Any insight would be great .
Thanks you ,
Hi @bwalton, thanks for your comment.
I've connected using a sql server connection into our applications server where the SysAid data is held.
Do you currently use SysAid, then? What's your opinions?
A
Hi @javirmerino ,
Sorry for delay in response, We can try to use the following calculated column to meet your requirement:
Required Completion Date =
IF (
[Required Completion Date] = BLANK ()
|| [Required Completion Date] = "",
VAR d = [Date]
VAR t = [Time]
VAR HourValue =
SWITCH ( [Urgency], "Urgent", 4, "Very High", 8, "Normal", 24, "Low", 40 )
VAR isWorkDay =
CALCULATE (
COUNTROWS ( 'Dim_Date' ),
FILTER (
'Dim_Date',
[Date] = d
&& [IsHolidayUK]
&& ( WEEKDAY ( [Date] ) IN { 1, 7 } )
)
) = 0
VAR beforeWorkTime =
t < TIME ( 9, 0, 0 )
VAR inWorkTime =
AND ( t >= TIME ( 9, 0, 0 ), t <= TIME ( 17, 0, 0 ) )
RETURN
SWITCH (
TRUE (),
isWorkDay && inWorkTime, d + t + HourValue / 24,
isWorkDay && BeforeWorkTime, d + TIME ( 9, 0, 0 ) + HourValue / 24,
CALCULATE (
MIN ( 'Dim_Date'[Date] ),
FILTER (
'Dim_Date',
[Date] > d
&& NOT ( [IsHolidayUK] )
&& NOT ( WEEKDAY ( [Date] ) IN { 1, 7 } )
)
)
+ TIME ( 9, 0, 0 ) + HourValue / 24
),
[Required Completion Date]
)
Best regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |