Forum Discussion

HarmVanG's avatar
HarmVanG
Icon for Advocate IV rankAdvocate IV
9 years ago
Solved

Change axis labels to a custom format

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

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    9 years ago

    @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/

     

     

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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