Calculate time that is within a range of times

[Anonymous]

Hello,

I am trying to calculate the availability of digital applications in different countries based on downtimes and working times in each country.

For example:

In my incidents table I have an incident registered from 11/01/18 6 AM to 11/01/18 10:00 AM the real duration is 4 hours but to calculate the availability I need to calculate the time that this incident imapcted each country based on their specific working times.

The working time defined for UK is from 9:00 AM to 6:00 PM so the real impact for this country was only from 9 to 10 (1 hour)

On the other hand, working time defined for Germany is from 7:00 AM to 5:00 PM so the real impact for this country was only from 7 to 10 (3 hours).

I  need to calculate the availability based on this specific durations for each country. At the end I'm loking to end with something like this:

Any help?

Thank you all so much.

[v-chuncz-msft]

@Anonymous,

You may refer to the DAX below.

Column =
SUMX (
    incident,
    MIN ( incident[to], working[to] ) - MAX ( incident[from], working[from] )
)

[Anonymous]

@v-chuncz-msft

Hi Sam,

I'm using the DAX:

RIncidentDuration = SUMX(
 'Incident Catalog',
 MIN('Service Schedule'[Finish],'Incident Details'[HFinish]) - MAX('Service Schedule'[Start],'Incident Details'[HStart])
)

However, I get the following error:

A single value for column 'Start' in table 'Service Schedule' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Here are my relationships:

Details:

• All incident details incluiding start and finish are in the Incident details table. However,
• One incident might affect severtal countries or applications, that's why I have my Incident catalog table. This contains IncidentID that repeats, as well as affected application and country.
• Services schedule table has the country code and the start and finish of the service schedule of each one.

I hope you can help me,

Best regards.

[Greg_Deckler]

Posting sample data that can be copied and pasted and sample output is always helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

That being said, take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365