Bar Chart Axis Label Issue

[Thiyagu]

Hi All,

How to show all months in the chart x-axis(July '15 - June '16).

I have created a dashboard based on financial year (ex 2015, 2016).

In this chart, the x-axis values shows like grouping(Jan, Apr, July). I want to show the labels as (Jan, Feb, Mar... Dec)..

Please Help..

I dont think, i have explained my query correctly, please let me know for further info.

Thanks

[Baskar · Accepted solution]

Hi Thiyagu,

Cool Buddy!!!

1. Create "Date Master " using the folowing Power Query

let
Source = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & "'" & Text.Range(Number.ToText([Year]),2,2)),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekStarting = Table.AddColumn(InsertWeekEnding, "WeekStart", each Date.StartOfWeek([Date]), type date)
in
InsertWeekStarting,
#"Invoked FunctionSource" = Source(#date(2016, 1, 1), #date(2016, 12, 31), null),
#"Changed Type" = Table.TransformColumnTypes(#"Invoked FunctionSource",{{"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayInWeek", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
#"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Date - Copy", Date.WeekOfYear}}),
#"Renamed Columns" = Table.RenameColumns(#"Calculated Week of Year",{{"Date - Copy", "WeekofMonth"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"DateInt", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Sorting Order", each [Year]*100 + [MonthOfYear]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each true),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Sorting Order", Int64.Type}})
in
#"Changed Type2"

2. Now you have create Relationship between your original source with Date Master

Eg. Date from Date Master = Your Fiscal Date

3.  Now Drag MonthInCalender to X-Axis

4. Then Choose "MonthInCalender"  go to "Modelling" and you find "Sort by Order"  from there u can choose "Sorting Order"

Let me know , if it is not solve your problem.

Cheers

[Thiyagu]

Thanks Baskar..

Its working now... Great.. 

Thank you Again Baskar

[ashishrj]

@Thiyagu You can try creating a new calculated column with the desired values and then mapping the X-axis with the new calculated column. 

[Thiyagu]

Hi ashishrj

Thank you for your response, first i tried the new calculated column using Concatenate(Month(FM) & "," & Year(FM)).

Am getting the solutions like this (1, 2015.. 10, 2015.. 11, 2015). PBI sort like this.. Is there any way to resolve this??

Thanks

[itayrom]

You could try changing the axis type to "Categorical" rather than "Continuous"(Format Pane > X-Axis > Type).

It will cause the following:

1. All date labels will be shown.

2. If there's not enough space, they'll be displayed vertically.

3. If there's still not enough space, the visual will become horizontally scrollable.

4. The line will be cut in places where it has no values in your data(Since it is not continuous in this mode).

[Thiyagu]

Hi itayrom

Thank you for your response, yes you're correct. but i my concern is to show all the labels like in this format (Jul 2015.. Apr 2016). Is there any possiblities?

Thanks

[itayrom]

A simple solution would be selecting your date field in the fields pane, going to the ribbon's modeling tab, clicking on "Format > Date/Time" and selecting the desired format(E.g. "March 2001 (MMMM yyyy)").

But this affects how the date will be displayed in every visual provided with that field.

A better solution, and generally a better practice, would be to create a "Date Master", as suggested by @Baskar. 

[ankitpatira]

@Thiyagu power bi automatically determines best possible way to display labels on x-axis. If you just make that visual bigger you should be able to see rest of the values as well as you're seeing bars but not the labels.

[Thiyagu]

Hi ankitpatira

Thank you for your respond, actually i can able to see the bars without expand it, but i need to show the labels to Customers. That is my concern..

Thanks