Plotting line with custom date hierarchy as x-axis

[sean_cochran]

EDIT: sample file here.

I have a custom date hierarchy with three layers: year, year + month, and day. I have this date hierarchy set as the x-axis on a line chart.

One of the metrics on the chart is blank in some periods by design (comparatio). I want a line plotted between these points, but it isn't:

I have read several other posts, and as I understand it, I must use a column with scalar values (date or number) and set the x-axis as continuous to see the line plotted. I have the relevant column set as a whole number:

The lines are plotted correctly on the top level (year) of the hierarchy:

But when I expand to the next level of the hirearchy (year+month), the option to set the x-axis as continuous disappears from the chart settings and the line is not plotted.

What am I missing to make this work? Is there anything I have misunderstood?

[lbendlin]

I have read several other posts, and as I understand it, I must use a column with scalar values (date or number) and set the x-axis as continuous to see the line plotted

Correct, whih means they all have to be date values.

sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/

[sean_cochran]

Are numbers not scalar?

[lbendlin]

Great question, and there is a big debate raging in the Vega community about the finer differences between "ordinal" and "nominal".  I think it all boils down to the continuity of the values.  Date values are by default assumed to have a natural order (the arrow of time) whereas all others could be random collections (ie categoricals)  (and yes, one could argue that at least integers have continuity - but someone decided otherwise).

[sean_cochran]

Same result when I use dates (I used EOMONTH to create dates) instead of whole numbers. Any ideas?

[lbendlin]

[sean_cochran]

I see you removed the date hierarchy and that you highlighted the NaN values. What should I be taking away from this?

[lbendlin]

Yeah, sorry, didn't finish - got sidetracked. But you will want to investigate the NaN issue.

Will check later today 

[lbendlin]

But - notice that the X axis is still categorical, and once you add Turnover  it will revert back to the dots as the granularity doesn't match between the two measures.

[sean_cochran]

Thanks for the follow up. I see you replaced my date hierarchy with a new one and zoomed into to a granularity that is based on text values (month abbreviation) rather than dates. This should result in a categorical x-axis, so your result is not surprising (unless I'm missing something?). Reverting back to my original hierarchy with scalar date values for the month-year level of granularity - I'm still confused why I can't get a continuous x-axis when the current level of my custom date hierarchy (month-year) is populated with scalar values. Is this because it is missing the metadata (that must be set with tabular editor) Mr. Russo mentioned at the bottom of his article? Regarding the NaN values - they were intentional since there isn't data for those periods and 0 isn't accurate. I was hoping for a solution that didn't require interpolation, but it sounds like that might be my best bet. What are your thoughts?

[lbendlin]

BLANK() is better than NaN.  Interpolation could solve this but do you really want to go there?

[sean_cochran]

Thanks for the tip. I replaced the NaN values with blanks.

I want to avoid interpolation. Is it impossible to plot both lines if one measure is missing values for some periods?

[lbendlin]

I think that's what it boils down to. I'll do some more tests on my side.

[sean_cochran]

Oddly, error bands are plotted correctly even though the line isn't:

Which means that I can just plot the measure itself as an error band line:

The downside of this approach is that the info bubbles get confusing, and you can't use the error bands anymore:

[lbendlin]

I think you may want to raise a ticket with the product team. Sure sounds inconsistent.

[sean_cochran]

I tried updating the metadata of my date hierarchy as Marco Russo described. Perhaps I made a mistake, but the x-axis did not change to continuous, so that seems like a dead end.

[sean_cochran]

Thanks for the background - I didn't realize Power BI was opinionated on this topic. I thought I tried date values as well, but I must have missed something. I'll try again with dates and report back.