Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have three different charts displaying data on weekly basis. I am looking to visually align the individual bars.
For example in the shanpshot included below, 2nd chart does not show any data related to Week of October 10 or October 24 because there simply wasn't any activity during those weeks. Yet, I would like to have the columns of the 2nd chart sit nicely under the colums of the first and vice-versa. Problem is even more evident when looking at the 3rd chart.
So in order to fix this problem, I presume that I need to pad certain datasets with certain zero values, don't I? What would be the most elegant way of reaching the X axis alignment among multiple charts?
Thank you!
Solved! Go to Solution.
There are quite a few "DimDate"/"Calendar" table generation patterns out there now - e.g. http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/ It seems you really need one to may full use of the DAX Time Intelligence functions.
The function script below cloned from that link above adds a "Week Starting" column as well as you seem to be using that. Cut out what you don't need or add other custom data dimensions - e.g. see https://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/ for Fiscal Years etc.
//Create Date Dimension (StartDate as date, EndDate as date)=> let //Capture the date range from the parameters StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), Date.Day(StartDate)), EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), Date.Day(EndDate)), //Get the number of dates that will be required for the table GetDateCount = Duration.Days(EndDate - StartDate), //Take the count of dates and turn it into a list of dates GetDateList = List.Dates(StartDate, GetDateCount, #duration(1,0,0,0)), //Convert the list into a table DateListToTable = Table.FromList(GetDateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), //Create various date attributes from the date column //Add Year Column YearNumber = Table.AddColumn(DateListToTable, "Year", each Date.Year([Date])), //Add Quarter Column QuarterNumber = Table.AddColumn(YearNumber , "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([Date]))), //Add Week Number Column WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", each Date.WeekOfYear([Date])), //Add Month Number Column MonthNumber = Table.AddColumn(WeekNumber, "Month Number", each Date.Month([Date])), //Add Month Name Column MonthName = Table.AddColumn(MonthNumber , "Month", each Date.ToText([Date],"MMMM")), //Add Day of Week Column DayOfWeek = Table.AddColumn(MonthName , "Day of Week", each Date.ToText([Date],"dddd")), //Add Week Starting Date (N.B. Need to force (default!) Sunday start of week for some reason) WeekStarting = Table.AddColumn(DayOfWeek, "Week Starting", each Date.StartOfWeek([Date], Day.Sunday), type date), //Add Week Ending Date (N.B. Need to force (default!) Sunday start of week for some reason) WeekEnding = Table.AddColumn(WeekStarting, "Week Ending", each Date.EndOfWeek([Date], Day.Sunday), type date) in WeekEnding
Save it as a Query named "CreateCalendarTable" or similar. Then call that function from a SECOND query called "DimDate" or "Calendar" etc. which gives you the populated table you need. For simplicity in this example, I've restricted my Calendar (per red below) to a week either side of the Min and Max vales for Date in the Data table "Table1", but you can adapt those to "Today plus a Year", a fixed date #date(2000, 1, 1), etc.
let Source = CreateCalendarTable(Date.AddDays(List.Min(Table1[Date]),-7), Date.AddDays(List.Max(Table1[Date]), 7)), ChangedTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Week Number", Int64.Type}, {"Month Number", Int64.Type}}), SortedRows = Table.Sort(ChangedTypes,{{"Date", Order.Ascending}}) in SortedRows
Then create a "Many to One (*:1)" relationship from Table1 to Calendar tables on the Date column. My test version uses a Cross Filter Direction of Both, but Single should also work and may be forced on you by your data model in some cases.
See how that goes.
Cheers.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
106 | |
87 | |
75 | |
66 |
User | Count |
---|---|
125 | |
114 | |
98 | |
81 | |
73 |