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

[dwightman2176]

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

[dwightman2176]

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

Thnaks

David

[_AAndrade]

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 · Accepted solution]

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

[_AAndrade]

You just need to dynamically configure the _Min and _Max variables according to your model. That's why I used calculate.

[_AAndrade]

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]

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]

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