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.
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?
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!
- Anonymous9 years agoNot applicable
Glad to help, as I learn as well...
- LauraDiepeveen8 years agoRegular Visitor
Hi Steve or anybody else :)
Would this also be an option for discrete variables (categories) instead of dates? I am working on a skills & expertise matrix heatmap and would like to be able to show the empty categories regardless of the applied filter. However, due to the amount of categories, including all null-combinations would result in over 70 million rows.
(I didn't do a deepdive into the DAX script yet, if this could work for me I could do ofcourse)
Thanks, Laura