Forum Discussion
How to align X axis of multiple charts? (adding zero data)
- Anonymous9 years ago
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 WeekEndingSave 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 SortedRowsThen 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.
GilbertQ, Anonymous thank you for your replies!
The chart values are coming from 3 different tables - 1 chart - 1 table
The Date dimension in all three tables is logically the same - 1st day of the week.
Creating a dummy "zero value table" with 52 rows, 1 for each 1st day of the week and appending to the 3 tables could be a solution, but that seems a little bit too brute force like.
It works for me if I simply link the data tables to a Date dimension table with a Week Starting column:
- milpro0119 years agoHelper ISteve, that's awesome - exactly what I am looking for! Thx for the screen captures!
As I am quite new to Power BI, could you please clarify what you mean by linking 2 tables? Is it just many to one relationship in both directions on that "Date" dimension or is it something else?- Anonymous9 years agoNot applicable
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 WeekEndingSave 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 SortedRowsThen 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.
- milpro0119 years agoHelper I
Anonymous, thank you so much for such a detailed reply - you pushed my understanding of PowerBI forward several orders of magnitude! Cheers!