Calendar table - can't change month to date type

[jnrezk]

Hi there! I have a calendar table that I created by the month & month name show up as text and not a date 

I would like to ideally show the month in mm/yyyy format but when I try to change it I can't there's an error saying it needs to be in text. I linked my file below - can you help me figure out how to change it or add it in date format? Otherwise, when I pull into visuals it doesn't sort it on date order.

https://drive.google.com/file/d/1SFnhsbm1luBqPGgjKZl3Ewf88hA5GZy-/view?usp=sharing

thanks!

[Icey · Accepted solution]

Hi @jnrezk ,

This won't work in the graph, unless you change the X-axis type from "Continuous" to "Categorical".

But this will show duplicated date. It is because its essence is still "Date" by day, not by month.

In addition, why doesn't the text format meet your needs?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey]

Hi @jnrezk ,

Try this:

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jnrezk]

Hi @Icey this works but when I go to make the graph it doesn't work correctly. January 2022 is at the beginning and the format isn't right?

I tried switching between date & date heirarchy too

[Icey · Accepted solution]

Hi @jnrezk ,

This won't work in the graph, unless you change the X-axis type from "Continuous" to "Categorical".

But this will show duplicated date. It is because its essence is still "Date" by day, not by month.

In addition, why doesn't the text format meet your needs?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jnrezk]

Hi thanks, because I need the graphs to work and the slicers to show rolled up months. Do you have a solution for that?

[Icey · Accepted solution]

Hi @jnrezk ,

I think this can also work, please check:

Month_Text = FORMAT([Date],"mm/yyyy")

YearMonth = FORMAT([Date],"yyyymm")

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jnrezk]

Wonderful. This worked. Many thanks 🙂

[jnrezk]

This error comes about when i do that 

[amitchandak]

@jnrezk , create a column

Month year = format([Date], "mm/yyyy")

and

Month year Sort = format([Date], "yyyymm")

Select the first column and from column tool , mark the second column as sort column

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

[jnrezk]

Hi @amitchandak  I don't think I tagged you in my response above but one of the formulas didn't work - could you advise? Thanks!