Measure refers to days not in the data source on the visual

[EM_Z]

Hi Team,

I am seeking for help to understand and fix an issue I am facing with a visual showing service delivery level for a day that there was no service provided and incorrectly calculated measure. 

My data source starts in March 2024 until now. One Case may have in the source data one or more items (due to different statuses)

The SLA is calculated as follows 

SLA_test = 1-[SLA (%)]

Components:

1) SLA (%) = (DIVIDE([Measure], [Total tickets]))

2)Measure = countrows(FILTER(VALUES('ABC'[Case Number]),[FILTERED_TIME]<=selectedvalue('ABC'[SLA])))

3)FILTERED_TIME = CALCULATE(SUM('ABC'[Sum_Businessdays_hours(in h)]),'ABC'[Status] IN {"Unassigned", "In Process"})

Issue 1: SLA_test on the visual shows 100% for days for which I have no data (January 2024, while data starts in March 2024; weekends, ...). Result is shown for all the days on the top right chart, while the SLA(%) shows correctly data only for days when service was provided.  As SLA_test depends on SLA(%) I totaly do not understand the matter with the days :(. 

Issue 2: The formula returns correct SLA target per type of the case. Please check the below snap shot - Time (h) is smaller than SLA target, however SLA_test calculation is wrong - 0%. 

I will appreciate if you could support with the solution.

Thank you!

[v-qiaqi-msftv]

Hi @EM_Z，

Have you solved your problem?

If you have any question, please feel free to contact me.

If you have resolved your problem, please consider posting your answer here and mark your reply as solved.

Thanks for your cooperation.

Best Regards,
Qiao

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[EM_Z]

Hello @v-qiaqi-msftv ,

Thank you for your follow up.

Unfortunately the issue still persist.

I am sharing sample data below.

In the real report my query merges a numerous files. I have implemented in the query a formula that devides the Business Hours column, so that I obtain the number. I indicate also what is SLA time (in BH) for each case number (using else if and various conditions - some items are defined by Type and Request Type, some by Case Reason and Case Sub-Reason).

Case Number

Case Time Metric Name

Status

Case Record Type

Type

Request Type

Case Reason

Reporting BG-Division

Date/Time Opened

Date/Time Closed

Business Hours

Total Hours

Parent Case: Case Number

Owner: Full Name

Case Sub-Reason

05453034

CTN-25-01-10-7339824

New

Customer Service Case

Customer Master Data

10.01.2025, 03:03

17.01.2025, 12:11

0 Days 1.80 Hours

0 Days 6.74 Hours

JKL

Customer Data Change

05453034

CTN-25-01-17-7455747

In Process

Customer Service Case

Customer Master Data

10.01.2025, 03:03

17.01.2025, 12:11

4 Days 6.39 Hours

7 Days 2.39 Hours

GHI

Customer Data Change

05453034

CTN-25-01-17-7455748

Customer Service Case

Customer Master Data

10.01.2025, 03:03

17.01.2025, 12:11

0 Days 00 Hours

0 Days 00 Hours

GHI

Customer Data Change

05479955

CTN-25-01-16-7434798

Draft

SAP Super User Support

Order Management

Mass Upload CMIR & MAT DET

HCBG - Multiple Divisions

16.01.2025, 11:02

17.01.2025, 12:02

0 Days 0.00 Hours

0 Days 0.00 Hours

XXX

05479955

CTN-25-01-16-7436415

Unassigned

SAP Super User Support

Order Management

Mass Upload CMIR & MAT DET

HCBG - Multiple Divisions

16.01.2025, 11:02

17.01.2025, 12:02

0 Days 1.55 Hours

0 Days 1.55 Hours

GHI

05479955

CTN-25-01-17-7455582

In Process

SAP Super User Support

Order Management

Mass Upload CMIR & MAT DET

HCBG - Multiple Divisions

16.01.2025, 11:02

17.01.2025, 12:02

0 Days 7.43 Hours

0 Days 23.43 Hours

GHI

05479955

CTN-25-01-17-7455583

SAP Super User Support

Order Management

Mass Upload CMIR & MAT DET

HCBG - Multiple Divisions

16.01.2025, 11:02

17.01.2025, 12:02

0 Days 00 Hours

0 Days 00 Hours

GHI

05480391

CTN-25-01-16-7436877

Draft

SAP Super User Support

Order Management

Mass Upload CMIR & MAT DET

HCBG - Multiple Divisions

16.01.2025, 13:04

17.01.2025, 11:50

0 Days 0.00 Hours

0 Days 0.00 Hours

XXX

05480391

CTN-25-01-17-7455279

Unassigned

SAP Super User Support

Order Management

Mass Upload CMIR & MAT DET

HCBG - Multiple Divisions

16.01.2025, 13:04

17.01.2025, 11:50

0 Days 6.67 Hours

0 Days 22.67 Hours

GHI

05480391

CTN-25-01-17-7455366

In Process

SAP Super User Support

Order Management

Mass Upload CMIR & MAT DET

HCBG - Multiple Divisions

16.01.2025, 13:04

17.01.2025, 11:50

0 Days 0.09 Hours

0 Days 0.09 Hours

GHI

05480391

CTN-25-01-17-7455367

SAP Super User Support

Order Management

Mass Upload CMIR & MAT DET

HCBG - Multiple Divisions

16.01.2025, 13:04

17.01.2025, 11:50

0 Days 00 Hours

0 Days 00 Hours

GHI

BR,

Magda

[v-qiaqi-msftv]

Hi @EM_Z,

Could you please share a bit more about your sample data?

Furthermore, I think you should add a condition like IF(ISBLANK()) to return BLANK() for days with no relevant data.

SLA_test =
IF(
    ISBLANK([SLA (%)]),
    BLANK(),
    1 - [SLA (%)]
)

This will prevent the visual from displaying 100% for dates where [SLA (%)] is blank or unavailable.

If you have any other questions please feel free to contact me.