Forum Discussion

dwightman2176's avatar
dwightman2176
Frequent Visitor
2 years ago
Solved

Graph/Table showing more dates than needed on x-axis

Hello everyone. I'm hoping someone can help a bit of a Power BI novice here.

 

I'm trying to build a visual using a Line and Clustered Column chart. The chart should contain the last 6 full calendar months' downtime as one data point and availability % as the other data point, using the incident end date on the x-axis for the date range. The issue I am having is that when I plot the downtime on the chart, it works - just shows the last 6 calendar months' data. When I add the availability %, though, the date range extends to the beginning of 2022 and through to the end of 2024. I've tried using filters, but the availability % doesn't seem to adjust.

I can get around the situation temporarily this month by adding a filter which excludes entries where downtime = 0, but this would then not show entries on the visual where we genuinely haven't had any downtime.


For a little more information....

  • On the Column Y axis I'm using the SUM of a calculated column - Downtime
  • On the Line Y axis I am using the % Availability Measure
  • On the X axis I am using the incident end date column to plot the dates using a calendar hierachy
  • I assume it's the availability calculation that is causing this, as it is outputting 100% for the months where there is no actual data? This is how this is being calculated:
    • % Availability = 1-SUM(Issues[Impact on Uptime (%)]
      • Impact on Uptime (%) = DIVIDE(Issues[downtime ], Issues[Mins in month]
    • Downtime = DATEDIFF(Issues[Impact Start Date], Issues[Impact End Date], MINUTE

 

Visual with just downtime

 

Visual with downtime and % availability

Any help would be massively appreciated, and please let me know if I need to include some additional information.

 

Thanks
David

Need Help
  • dwightman2176's avatar
    dwightman2176
    2 years ago

    Thanks again . I played around with your suggestion and eventually came up with the following:

    6-Month Availability % =
        var _MinDate = TODAY()-184
        var _MaxDate = TODAY()
        RETURN
            IF(
                ENDOFMONTH(
                    Issues[End Date.[Date]) <= _MinDate,
                    BLANK(),
                    IF(ENDOFMONTH(Issues[End Date].[Date]) > _MaxDate,
                    BLANK(),
            'Issue Measures'[% Availability]
            ))

    Its not the perfect solution as it only works on a report looking at the past 6 months, but it will do for now until we can come up with something more flexible.

     
    Thanks again for your help,
    David

7 Replies

  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    I think the problem is on "% Availability" measure. Put some IF function that only do the "% Availability" if Downtime has value.

    Something like this:
    IF(NOT(ISBLANK( [Downtime])), 1-SUM(Issues[Impact on Uptime (%)], BLANK())

    • dwightman2176's avatar
      dwightman2176
      Frequent Visitor
      2 years ago

      Thanks _AAndrade . That has almost solved it. Definitely a step forward. The issue I am left with now is where there is no downtime in a given month, that month is not presented in the visual, whereas I would like it to just show as being blank. As you can see in the attached - December is not presented on the visual

       

      Thanks,

      David

      • _AAndrade's avatar
        _AAndrade
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        Replace BLANK() to 0 (zero). I think it could solve the problem

  • dwightman2176's avatar
    dwightman2176
    Frequent Visitor
    2 years ago

    Thanks again _AAndrade . That fixes the December issue, but re-introduces the other dates into the visual

     

    Thnaks

    David

    • _AAndrade's avatar
      _AAndrade
      Icon for Resident Rockstar rankResident Rockstar
      2 years ago

      Probably to do what you want, you need to see the min and max date of the x-axis that has value and after apply the measure.
      For example:
      VAR _MinDate = CALCULATE(MIN(Date), [measure] <>0)
      VAR _MaxDate = CALCULATE(MIN(Date), [measure] <>0)
      RETURN
      IF(
         Selectvalue(Date) >= _MinDate && Selectvalue(Date)<= MaxDate,
         [measure],
        Blank()
      )

      Probably the measure that I wrote need to fix some issues but the idea is this.

      • dwightman2176's avatar
        dwightman2176
        Frequent Visitor
        2 years ago

        Thanks again . I played around with your suggestion and eventually came up with the following:

        6-Month Availability % =
            var _MinDate = TODAY()-184
            var _MaxDate = TODAY()
            RETURN
                IF(
                    ENDOFMONTH(
                        Issues[End Date.[Date]) <= _MinDate,
                        BLANK(),
                        IF(ENDOFMONTH(Issues[End Date].[Date]) > _MaxDate,
                        BLANK(),
                'Issue Measures'[% Availability]
                ))

        Its not the perfect solution as it only works on a report looking at the past 6 months, but it will do for now until we can come up with something more flexible.

         
        Thanks again for your help,
        David