How to change Month & year format in X-Axis?

[Anonymous]

My current graph is showing the Year & Month in "2019 July" format. But Iwant to show as "Jul 2019"/"Aug 2019" format. How can I show all of my Month & Year in X-Axis in "Jul 2019"/"Aug 2019" format? Please, see screenshot to understand my current month & year format.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

MainTable:

mod = MOD('MainTable'[Month_value],2)

2. Create calculated table.

Table 2 =
SUMMARIZE('MainTable','MainTable'[Year_Month],'MainTable'[Year],'MainTable'[Month_value],'MainTable'[mod])

3. Create calculated column.

Calculated_table:

Group =
CALCULATE(SUM('Calculated_table'[mod]),FILTER(ALL('Calculated_table'),'Calculated_table'[Year]=EARLIER('Calculated_table'[Year])&&'Calculated_table'[Month_value]<=EARLIER('Calculated_table'[Month_value])))

Flag =
var _last=
CALCULATE(MAX('Calculated_table'[Year_Month]),FILTER(ALL('Calculated_table'),'Calculated_table'[Year]=EARLIER('Calculated_table'[Year])&& 'Calculated_table'[Group]=EARLIER('Calculated_table'[Group])&&'Calculated_table'[mod]=1))
var _next=
CALCULATE(MAX('Calculated_table'[Year_Month]),FILTER(ALL('Calculated_table'),'Calculated_table'[Year]=EARLIER('Calculated_table'[Year])&& 'Calculated_table'[Group]=EARLIER('Calculated_table'[Group])&&'Calculated_table'[mod]=0))
return
_last &"/"&_next

4. Create measure.

Measure =
var _yearmid=
VALUE(
RIGHT(MAX('Calculated_table'[Flag]),4)
)
var _monthmid=
MID(MAX('Calculated_table'[Flag]),10,3)
var _monthright=
LEFT(MAX('Calculated_table'[Flag]),3)
return
CALCULATE(SUM('MainTable'[rand]),FILTER(ALL('MainTable'),'MainTable'[Year]=_yearmid&&'MainTable'[Month]=_monthmid))
+
CALCULATE(SUM('MainTable'[rand]),FILTER(ALL('MainTable'),'MainTable'[Year]=_yearmid&&'MainTable'[Month]=_monthright))

5. Result:

Best Regards,

Liu Yang

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

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Write this calculated column formula in the Calendar Table

Month_year = format(Calendar[date],"mmm yyyy")

Drag the Month_year column to the X-axis of your visual.

Hope this helps.

[Anonymous]

@PC2790 But, I want show the short form of month. Such as, "Jan 2019"/"Feb 2019" instead of "January 2019"/"February 2019". Also, want to show the serially. Such as, Jan 2019, Feb 2019, March 2019, ..., Jan 2020, Feb 2020, March 2020,..., Jan 2021, Feb 2021, March 2021... . Can you please help me with that?

[PC2790]

Hi @Anonymous ,

Try adding them individually rather than as a heirarchy.

Outcome will be: