Change axis labels to a custom format

[HarmVanG]

Dear community,

I'm currently working on converting old excel dashboards into Power BI at the company that I work.

There is an issue however that I'm currently have to work with data that only has years and months, though they are formatted as a number and string in seperate collumns.

One of the tasks that I've had was to create a chart that shows the last 13 months on a line chart. I've done this by creating a month number from the strings in the month collumn, and then combining the year+month to use for the X-axis.

The business however would like to see a simple notation of jan, feb, mar etc.

They would also like to see a filter/slicer for quarters in the future.

Is there a way to easily do this?

Thanks in advance,

Harm

[v-yulgu-msft]

Hi @HarmVanG,

Was above reply helpful to your scenario? If so, please kindly mark the corresponding reply as an answer so that it can benefit more people. If you still have any concern, please feel free to ask.

Best regards,
Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @HarmVanG,

Here is an other option that can achieve your requirement. The goal is to generate a date column, so, we first create calendar table. You don't need to create a relationship between date table and source table.

Dim date =
FILTER (
    CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) ),
    DAY ( [Date] ) = 1
)

Then, in your source table, suppose, currently, you have had two columns [Month] and [Year], both are whole number type. Now, you need to create a calculated column.

Day =
LOOKUPVALUE (
    'Dim date'[Date],
    'Dim date'[Date].[Year], 'Year-Month'[Year],
    'Dim date'[Date].[MonthNo], 'Year-Month'[Month]
)

Best regards,
Yuliana Gu

[jkerski]

My company recently released a free custom visual on App Source that allows custom labels on column charts.  https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA200001785?tab=Overview  With the advanced editor you can pick which columns or DAX measures to use for the labels.

[vanessafvg]

@hat i would do @HarmVanG is create a date table,  conver the month-year column, transform the year month to a 1 of every month and then you have the functionality fo what comes with a date table

apparently this creates a date column but i could be wrong i copied from elsewhere and didnt' validate

InsertedCustom = Table.AddColumn(Source, "Custom", each Date.FromText([Month] & " 15," & Number.ToText([Year])))

and then link it to this

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/