Line chart date hierarchy assistance

Hi Anonymous,

Hopefully I'm understanding this correctly, you're looking for a smoother line at a monthly grain? If I haven't understood properly, then you might find this useful either way for something else further down the line 😛

TL/DR: use a date field rather than a hierarchy, but resolve it consistently for each month. At date table will also help when doing this, if you're not using one.

Longer Answer

We might need a sample of your data to get it 100% right, but if the data is at a monthly grain, the way I usually tackle it is to snap all data to a constant position in the month - either the start or the end.

Which you would choose would depend on your requirements and whether it makes sense for the data to be visualised this way (i.e. it would be okay for monthly financial reporting where items are invoiced at various days in the month but you'd want to resolve total spending to that period end).

If the date is not resolved, then the date will be plotted as per the data point's date value, and this results in many more unique x-values across your axis, as each measurement is taken on a different day within that particlar month.

I'll try and illustrate with a simple example. Here's a simple plot where the measure date is not resolved, and the number of distinct date values across all series is much higher than 12:

Notice that the lines for each start and end differently, due to how the day for each measurement is recorded within each month.

You can also see it in the chart tooltips - note that I only get a value for one series for this date, because I only have one measurement at that point along the x-axis:

I'll now add a column to my date table that creates an 'end of month' date for the date that's joined, e.g.:

Month End Date = EOMONTH(Date'Date', 0)

If I now plot by this date, I get the following:

The measure values are actually the same as above; they just line up the same along the x-axis, giving less unique x-values in your bottom chart than the top. The card measure to the right illustrates this, and here's a tooltip for the same month, showing all three series:

At this point, a comparison between the two doesn't look too weird, but if I let this out to a number of years, then I see a more pronounced difference between the graphs, e.g.:

Now, we see that there are many more X-Values for the chart that's as per the date and it starts to look a little more erratic. The values are actually the same, but the X-Value is resolved to the end of the month.

Hopefully this makes sense and is applicable to your situation. Good luck!

Daniel

If my post helps, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂