Adding Year to Month for Dates (on x axis)

[ElliotP]

Hi,

Photo: https://gyazo.com/1b127b2d206730094af6dc6ec2fc65c0

I'm trying to add the Years to the end of the months for my graph. For my data it's typed as a Date as well as it's in the nice format 1/7/2015.

The Data starts from July 2015 and goes for a year from there so it finishes up in June of 2016; yet the graph shows it as January, Feb, etc with no regard for the different Year.

I'd some advice on how to sort it as so it appears from July 2015 onwards as well as how to maybe edit what the x axis is labelled as.

I've read a lot of guides online, spent quite a bit of time trying the different visualisation features offered; but as you can see from the photo, regardless of which type of graph I pick (data is in the order I'd like it to be from July 2015) I don't have those options available for some reason.

Any help would be greatly appreciated.

[carinat]

I think this is the same problem that I had some weeks ago - just with dates.

The build-in hierarchy is causing this so you would need to add a calculated column with month  (=MONTH(table[date column]) ) and format it as "mmmm yyyy" (or whatever you'd like) in the modelling tab. Add that column to your axis instead (if you still want drilling you can also make your own hierarchy instead of the build-in one) and make sure it's on the column instead of hierarchy by the axis settings:

[ElliotP]

Good Evening,

Here is the code I used:

CalcMonth = FORMAT('All Web Site Data'[Date - Copy], "MMMM-YYyy")

Works perfectly thank you very much.

In terms of heirarchy, do I just repeat the same steps and build the heirarchy the same and it will produce the same feature as if I were to drill down?

The only issue left is to have the graph start from the first datapoint I have and go fowards; so from July 2015 to June 2016, not Jan2016-July2015.

My gyazo isn't working atm otherwise I'd attach a pic for you!

[carinat]

In Modeling, you can choose Sort By Column and choose your date column - that will make them appear in the right order.

For more about hierarchies, you can watch the following introduction: https://www.youtube.com/watch?v=q8WDUAiTGeU

I think you'll need to actually make a column with "= MONTH(date column)" and similar with year, date and quarter and then format them in the modelling tab instead of adding the format to your formula.

[ElliotP]

I tried sorting the visual as well as in the Data view using the model tab and it seems to have no effect.

Photos:

https://gyazo.com/21a88c384724436e00911b155f9e161a

https://gyazo.com/a477b3ecbdd867aff15de7b6237719e4

When I try to sort the Calc'd Month column by the date it gives me this error;

https://gyazo.com/8f0ac443538bda37ee78d27a02ac93de

[carinat]

That's right. Then you can make yet another column formatted differently so that it gives you something to sort by (2016-01).

[ElliotP]

I'm still getting the same error when i'm trying to format month, it seems you really can't format the same month value for multiple individual dates;

Ideas? I'll try a few things and see what I come up with

[ElliotP]

I've added in my original date column, that serves it seems as the month hierachy level, simply because it has so many data points.

I'd like to be able to do Day, Month, Quarter, Year.

I'm certain there must be a workout for this; this seems really silly if there isn't.

[ElliotP]

If we do it year first, it sorts the yr and then alphabetically and we can't change the data type to date either.

For Example it transforms to:

15-Aug

15-Dec

Instead of:

15-July

15-Aug

15-Sept

[ElliotP]

I've tried a few things from creating a new table, changing the type to date (messes up the month format), nothing seems to be working.

I get the feeling we're missing something really little here as to how to get around the error.

It's funny that the natural drill down function doesn't allow for this.

[ElliotP]

I've just added a day column, that sorted and got past the error.

It's now a hierarchy thing, working out how to create a calcualted column to put in the hierarchy that will work with it.