Calculating Availability (duration as a percentage of time range)

[beerygaz]

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 

[v-yulgu-msft · Accepted solution]

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]) )

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]

Best regards,

Yuliana Gu

[GilbertQ]

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

[beerygaz]

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.