Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
v-kered
Regular Visitor

Calculating Incident per daily/ weekly/ monthly

Hi,

 

I am calculating IPD. To calculate IPD, I need number of working days.

1) I have a date field "Date" (when the ticket came in)

2) I have a triad field(tell us whether the ticket is an enrollment or App Deployment etc..)

3) Ticket number, title ,CreatedIsWorkingday, etc..etc..

The problem I am having is:

I have created a measure: Measure = CALCULATE(DISTINCTCOUNT(Query2[Date]),'Query2'[CreatedIsWorkingday] ="Yes")

and calculated IPD measure: IPD = DIVIDE(COUNTA(Query2[Ticket Number]),Query2[Measure],0)

These 2 measure is helping me to get the IPD Capture.JPG

 

when I break this by Triad: Consider april 2017: IPD is 101.86. But after giving a column series "Triad". When I sum up, I dont get the same count as allup.

Capture.JPG

 

The reason behind is

Measure = CALCULATE(DISTINCTCOUNT(Query2[Date]),'Query2'[CreatedIsWorkingday] ="Yes")

this above measure is calcualting only if the date is available. Let's suppose if we dont have any ticket on april 11th(Date) then it is not considering as a working day.

 

inBrief:

This is how I want (7 Working days)

Capture.JPG

but after breakdown: This is what I am getting. I dont have a ticket on 11th for triad "Enrollment"(Traid as a slicer) . so it is not giving me total working days.

Capture.JPG

How can we fix this? Can anyone suggest a best way to resolve this issue.

2 REPLIES 2
Eric_Zhang
Employee
Employee


 

The reason behind is

Measure = CALCULATE(DISTINCTCOUNT(Query2[Date]),'Query2'[CreatedIsWorkingday] ="Yes")

this above measure is calcualting only if the date is available. Let's suppose if we dont have any ticket on april 11th(Date) then it is not considering as a working day.

 

inBrief:

This is how I want (7 Working days)

Capture.JPG

but after breakdown: This is what I am getting. I dont have a ticket on 11th for triad "Enrollment"(Traid as a slicer) . so it is not giving me total working days.

Capture.JPG

How can we fix this? Can anyone suggest a best way to resolve this issu


The latter case is what I'd expect since there's no ticket on 11th Apr.

 

To get your expected output, you can check reply from @Habib in this similar case.

Thank you! One of the best responses I've seen in this forum! Brief, on-point, and actually addresses the issue! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.