Forum Discussion

brokencornets's avatar
brokencornets
Icon for Helper IV rankHelper IV
7 years ago
Solved

Calculating availability in a measure

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!

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

4 Replies

    • brokencornets's avatar
      brokencornets
      Icon for Helper IV rankHelper IV
      7 years ago

      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's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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