Calculating availability in a measure

[brokencornets]

Hi

I'm trying to create some asset availability reports and I'm a little bit stuck, I was hoping someone could point me in the right direction.

Basically I have a list of jobs opened over the last two years with start and end dates, and a list of all assets. From this, I can easily calculate current availability based on # of assets and # of open jobs. I can also calculate historical availability for the last 2 years by adding a calculated column for End Date-Start Date.

HOWEVER, what I'd like to be able to do is have a date slicer where we can pinpoint different timeframes and get the availability over any period in the last 2 years. I know a calculated column won't give me the flexibilty to do that as it won't change with the slicer, right? So I figure I'll need a measure but I don't really know how to do this.

I'm thinking something along the lines of:

Measure = min('Jobs'[Date Completed],max(Dates[Date]))-max('Jobs'[Date Raised],min(Dates[Date]))

but that just isn't working at all. Any help would be much appreciated!

[v-frfei-msft]

Hi @brokencornets,

Kindly share your sample data and excepted result to me.

Regards,

Frank

[brokencornets]

Hi Frank

I have a table called Jobs with 'Date Raised' and 'Date Completed' fields, along with a Date table feeding a date slicer.

So for instance, jobs might look like:

Job    Asset       Raised        Completed

123    ABC         1-Oct-18     17-Oct-18

456    XYZ         10-Oct-18   14-Oct-18

So what I need to measure is the downtime - so 16 days for job 1 and 4 days for job 2, 20 days total. But if the date slicer was set to, say, 7-Oct to 11-Oct, I'd want the measure to record that asset ABC was unavailable for 4 days and asset XYZ for 1 day during that period, for a total of 5 days.

Hopefully that clarifies a bit, but if you need any more info please let me know!

[v-frfei-msft · Accepted solution]

Hi @brokencornets，

Create measures as below.

Measure =
VAR maxdate =
    MAX ( 'date'[Date] )
VAR mindate =
    MIN ( 'date'[Date] )
VAR maxrad =
    MAX ( Table1[Raised] )
VAR maxcom =
    MAX ( Table1[Completed] )
RETURN
    IF (
        mindate <= maxrad
            && maxdate >= maxcom,
        DATEDIFF ( maxrad, maxcom, DAY ),
        IF (
            mindate <= maxrad
                && maxdate <= maxcom,
            DATEDIFF ( maxrad, maxdate, DAY ),
            IF (
                mindate > maxrad
                    && maxdate >= maxcom,
                DATEDIFF ( mindate, maxcom, DAY ),
                IF (
                    mindate > maxrad
                        && maxdate < maxcom,
                    DATEDIFF ( mindate, maxdate, DAY ),
                    IF ( mindate > maxcom, 0 )
                )
            )
        )
    )

Measure 2 = SUMX(ALL(Table1),[Measure])

For more details, please check the pbix as attached.

Regards,

Frank

[brokencornets]

That's awesome, thanks!

I had to make some adjustments - to factor in jobs closed before the selected date range, opened after the selected date range, and not yet closed - but I would never have got there without your help!