Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
dwightman2176
Regular Visitor

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

dwightman2176_1-1708593945803.png

 

Visual with downtime and % availability

dwightman2176_0-1708593902116.png

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

 

Thanks
David

1 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]
        ))
dwightman2176_0-1708686154361.png

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

View solution in original post

7 REPLIES 7
dwightman2176
Regular Visitor

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

 

dwightman2176_0-1708613591900.png

Thnaks

David

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.

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]
        ))
dwightman2176_0-1708686154361.png

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

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

_AAndrade
Super User
Super User

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

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

 

dwightman2176_0-1708605480873.png

Thanks,

David

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.