cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calculate time that is within a range of times

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:

 

Capture.PNG

 

Any help?

 

Thank you all so much.

 

 

3 REPLIES 3
Super User
Super User

Re: Calculate time that is within a range of times

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: Calculate time that is within a range of times

@Anonymous,

 

You may refer to the DAX below.

Column =
SUMX (
    incident,
    MIN ( incident[to], working[to] ) - MAX ( incident[from], working[from] )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Calculate time that is within a range of times

@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:

Capture.PNG

 

 

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.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 349 members 3,249 guests
Please welcome our newest community members: