Line chart date hierarchy assistance

[Anonymous]

Hi everyone.

I'm dealing with an issue that I have come across many times before but I can't for the life of me put all the steps together.

I want to display the previous data based on a monthly number (not day-to-day). However... the only charts I am able to display are the ones below: 

The first image I am using the Date Hierarchy option in the line chart field. I left day and month selected.

The second one is using my "Date Master" column which is formatted at "Tuesday, January 12th 2019" for example.

Please let me know what othe rinformation you may need. Thanks!

How can I make it look like the first one with the date range from the second?

[dm-p]

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 🙂

[Anonymous]

So I probably should've clarified that the data I am reporting on the graph is ca calulated percentage. I am looking to smooth out the 100% and 0% that usually occur on weekends or holidays due to a small amount of data points. I need to report on a monthly level but distinguish year to year. Right now I can display by month using Data Heirarchy but it groups together the years. This is useless unless I add a slicer but I'd rather keep the dashboard clean. I am also able to display using the original Data as the Axis but this gives the breakdown day by day which is also useless as it tells only a very very small part of the monthly "story"