cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
beerygaz
Frequent Visitor

Calculating Availability (duration as a percentage of time range)

I have a table of incidents that contains the following fields:

 

name : string

lostservice : timestamp

regainedservice :timestamp

 

I'd like to be able to allow the user to select a date range using a slider and then show the duration and the duration as a percentage of the total time range for each entry.

 

I can manage calculate duration quite easily for the divident, but I'm not sure how to get the start and end dates from the slicer in order to calulate the time span for the divisor 

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

Hi @beerygaz,

 

Please new a calendar table first which is unrelated with the actual data table. Later, you should add [Date] field from this table into slicer.

Date = CALENDAR(MIN(data[lostservice]),MAX(data[regainedservice]) )

1.PNG

 

Create measures similar to below.

date range =
VAR startdate =
    MIN ( 'Date'[Date] )
VAR enddate =
    MAX ( 'Date'[Date] )
RETURN
    DATEDIFF ( startdate, enddate, DAY ) + 1

percentage =
VAR startdate =
    MIN ( 'Date'[Date] )
VAR enddate =
    MAX ( 'Date'[Date] )
VAR lostdate =
    SELECTEDVALUE ( data[lostservice] )
VAR regaineddate =
    SELECTEDVALUE ( data[regainedservice] )
RETURN
    IF (
        lostdate >= startdate
            && regaineddate <= enddate,
        DATEDIFF ( lostdate, regaineddate, DAY ),
        IF (
            lostdate >= startdate
                && lostdate <= enddate
                && regaineddate >= enddate,
            DATEDIFF ( lostdate, enddate, DAY ),
            IF (
                lostdate < startdate
                    && regaineddate <= enddate
                    && regaineddate >= startdate,
                DATEDIFF ( startdate, regaineddate, DAY ),
                IF (
                    lostdate < startdate
                        && regaineddate > enddate,
                    DATEDIFF ( startdate, enddate, DAY ),
                    BLANK ()
                )
            )
        )
    )
        / [date range]

2.PNG3.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Microsoft
Microsoft

Hi @beerygaz,

 

Please new a calendar table first which is unrelated with the actual data table. Later, you should add [Date] field from this table into slicer.

Date = CALENDAR(MIN(data[lostservice]),MAX(data[regainedservice]) )

1.PNG

 

Create measures similar to below.

date range =
VAR startdate =
    MIN ( 'Date'[Date] )
VAR enddate =
    MAX ( 'Date'[Date] )
RETURN
    DATEDIFF ( startdate, enddate, DAY ) + 1

percentage =
VAR startdate =
    MIN ( 'Date'[Date] )
VAR enddate =
    MAX ( 'Date'[Date] )
VAR lostdate =
    SELECTEDVALUE ( data[lostservice] )
VAR regaineddate =
    SELECTEDVALUE ( data[regainedservice] )
RETURN
    IF (
        lostdate >= startdate
            && regaineddate <= enddate,
        DATEDIFF ( lostdate, regaineddate, DAY ),
        IF (
            lostdate >= startdate
                && lostdate <= enddate
                && regaineddate >= enddate,
            DATEDIFF ( lostdate, enddate, DAY ),
            IF (
                lostdate < startdate
                    && regaineddate <= enddate
                    && regaineddate >= startdate,
                DATEDIFF ( startdate, regaineddate, DAY ),
                IF (
                    lostdate < startdate
                        && regaineddate > enddate,
                    DATEDIFF ( startdate, enddate, DAY ),
                    BLANK ()
                )
            )
        )
    )
        / [date range]

2.PNG3.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GilbertQ
Super User
Super User

Hi there

What you can do in your measure is the following for the min and max dates

The measure below will give you the total days based on what you selected on your Slicer

 

My Measure =
VAR MinDate = MIN(Table[lostservice])
VAR MaxDate = MAX(Table[lostservice])
VAR TotalDays = DATEDIFF(MinDate,MaxDate,Day)
RETURN
TotalDays




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

Proud to be a Super User!







Power BI Blog

Unfortunately that doesn’t work. The user wants to select a time window. The min and max measures will only give me entries in the table that exist. For example. A time range from 1 Jan to 7 Jan but we only have two outages on the 5th for 10 minutes. Inner to calculate the downtime as a percentage of the range selected (a week) but the measure will just return the start time of thenoutage on the 5th.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors