SLA visual to display breach or complaint records based on Start and End date

[sizi]

Dear all,

I need your help in visualizing SLA status on pie chart, where i want to show number of task thats "Breached or Complaint ".

The scenario is I have multiple dates from one record and my current date filter when filtered will show the tasks even if one date in a task falls in the date selection. My sample data is as shown below:

Task Name	Planned Date	Planned End Date	Start date	End Date	Initial Date	final Date	SLA Status
Task1	1-Mar-24	5-Mar-24	1-Mar-24	7-Mar-24	9-Mar-24	11-Mar-24	Breached
Task2	1-Feb-24	5-Feb-24	2-Feb-24	7-Feb-24	9-Mar-24	11-Mar-24	Complaint
Task3	1-Jan-24	5-Jan-24	5-Feb-24	15-Feb-24	9-Mar-24	11-Mar-24	Breached
Task4	1-Jan-24	10-Jan-24	2-Jan-24	7-Jan-24	9-Mar-24	11-Mar-24	Complaint

Now my issue is when i apply date filter to show last 2 months data, the visual shows breach count is 2 and Complaint count is 2 which shouldnt be the case. It should show complaint = 1 and breached = 2 as i want the SLA display to be only based on Start and End date and task 4 does not fall in the date range category eventhough it has current date in the task. SLA is calculated based on Start and End date .Kindly help as i am not able to figure out a way around this problem.

Thanks,

[Sahir_Maharaj]

Hello @sizi,

Can you please try the following DAX:

Breached Count = 
CALCULATE(
    COUNTROWS('YourTable'),
    'YourTable'[SLA Status] = "Breached",
    NOT(ISBLANK('YourTable'[Start date])),
    NOT(ISBLANK('YourTable'[End Date])),
    USERELATIONSHIP('YourTable'[Start date], 'CalendarTable'[Date]),
    USERELATIONSHIP('YourTable'[End Date], 'CalendarTable'[Date])
)

Compliant Count = 
CALCULATE(
    COUNTROWS('YourTable'),
    'YourTable'[SLA Status] = "Complaint",
    NOT(ISBLANK('YourTable'[Start date])),
    NOT(ISBLANK('YourTable'[End Date])),
    USERELATIONSHIP('YourTable'[Start date], 'CalendarTable'[Date]),
    USERELATIONSHIP('YourTable'[End Date], 'CalendarTable'[Date])
)

Hope this helps!

[sizi]

Hi Sahir,

Thank you for looking into this. However the dax is giving me error as below:

USERELATIONSHIP function can only use the two columns references participating in relationship.

Please note my date filter is defined to consider any single date available in the task and i want sla to show the records only if start and end date is within the date filter selected even if the task has recent date which falls under date filter range, it should exclude.