- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- % Availability = 1-SUM(Issues[Impact on Uptime (%)]
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks again . I played around with your suggestion and eventually came up with the following:
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.
David
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks again @_AAndrade . That fixes the December issue, but re-introduces the other dates into the visual
Thnaks
David
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
Did I answer your question? Mark my post as a solution! Kudos are welcome.
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks again . I played around with your suggestion and eventually came up with the following:
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.
David
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You just need to dynamically configure the _Min and _Max variables according to your model. That's why I used calculate.
Did I answer your question? Mark my post as a solution! Kudos are welcome.
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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())
Did I answer your question? Mark my post as a solution! Kudos are welcome.
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Replace BLANK() to 0 (zero). I think it could solve the problem
Did I answer your question? Mark my post as a solution! Kudos are welcome.
Proud to be a Super User!

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
01-23-2024 02:50 AM | |||
07-23-2024 03:32 AM | |||
05-15-2024 11:16 PM | |||
03-12-2024 05:57 AM | |||
08-09-2024 08:52 AM |
User | Count |
---|---|
141 | |
115 | |
82 | |
63 | |
48 |