Stacked area showing zeros

[brokencornets]

Hi guys, I'm being driven insane trying to do what I thought should be a very simple task.

I have an area graph showing jobs outstanding by priority over a period of time:

 

But as you can see on the image, the lines above the areas don't drop down to zero when there are no outstanding jobs of that type for that month (this is calculated using a measure).

Adding '+0' the the end of the measure almost gets me there, however it then shows zeros all the way through to the end of 2020 (and from the start of 2018 if I start my data from October 2018), even though my date table only goes to TODAY():

 

How do I get the chart to stop at the current month without hard-coding it in every month?

[v-eachen-msft]

Hi @brokencornets ,

You could add an IF() formula in your measure.

IF (
    SELECTEDVALUE ( 'Date'[Date] ) <= TODAY (),
    [Measure]+0
)

[brokencornets]

I couldn't work out how to build this inside the measure itself, but I created a new measure:

O2= IF (
    SELECTEDVALUE ( 'Date'[Date] ) <= TODAY (),
    [Outstanding]+0
)

 but the results don't change unfortunately.

[amitchandak]

@brokencornets , check you Axis type, try to make continuous from categorical

Without adding 0.

[brokencornets]

Hi Amit. The axis is already set to continuous, thanks for the suggestion though!

[parry2k]

@brokencornets yes it should be on date table and it should work, can you share the pbix file with sample data and remove any sensitive information before sharing. 

[brokencornets]

Really difficult to do because of the nature of the data, but if all else fails I might have to give this a try using some dummy data.

[parry2k]

@brokencornets help us to get help. If it is difficult for you, not sure how we can help.

[parry2k]

@brokencornets seems like that is a report published on your power bi instance, why not sure pbix file using one drive/google drive or simply send it by email, my email is in the signature. Just a reminder, remove any sensitive information before sharing.

[brokencornets]

E-mailed you now, thanks 🙂

[parry2k]

@brokencornets solution is already emailed back to you. you have to mark your date table as a date table to make it work which was missing.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[brokencornets]

You're a star, thanks - could you maybe could talk me through exactly what I need to change, as when I try to open your solution I get an error stating 'Object reference not set to an instance of an object'?

[parry2k]

@brokencornets mark date dimension as a date table, select date table, on table tools you will see the option Mark as a date table.

Not sure why you cannot open the pbix file, are you June 2020 release.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[brokencornets]

Ahh okay, I see it now. That's fixed the issue with the x-axis, but I don't seem to be able to show any level of granularity other than daily now - the date heirarchy disappears. I ideally want to be able to plot monthly, with the ability to drill into daily if required, and that functionality has now gone.

Sorry to be a pain! I really do massively appreciate your help!

[parry2k]

@brokencornets yes, it will disappear which is fine, and you can create your own custom hierarchy and in my pbix that is what I did. There are tons of posts on how to create your own hierarchy and here is the link to one such post

https://stoneridgesoftware.com/creating-hierarchies-in-power-bi/

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[brokencornets]

Hi. Back to be a pain in the bum again.

I've tried creating my own heirarchies and I've also been able on access your solution on my laptop - but while this approach seems to fix the original problem, it creates others. When I filter down to Month, the months appear to be sorted alphabetically, and also it's not possible to change the access from categorical to continuous, which makes the axis look less pretty, especially on the size of chart I'm looking at (there'll be around 9 of these to a page).

Does anyone else have any other ideas, or do I have to go down the laborious route of updating the end date of the charts every month?

[parry2k]

@brokencornets look at my blog post on how to correctly sort the months

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[brokencornets]

Looks really useful, I've bookmarked the website for future. However, it doesn't (as far as I can tell) solve the issue of not being about to show the x-axis as continuous, which (given the size of my visualizations) means if I have more than 14 months of data the user will have to scroll left or right to see all the data, which is a bit of a deal-breaker.

[parry2k]

@brokencornets once you drill down to the date level, you will able to change x-axis type to category and it will work. Let's be very clear here, change the table as a date table wouldn't have an impact on the visualization, you can still achieve the same, it is matter of doing it the right way.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[brokencornets]

Thanks, much appreciated!