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.
Glad to help, as I learn as well...
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