Forum Discussion

milpro011's avatar
milpro011
Helper I
9 years ago
Solved

How to align X axis of multiple charts? (adding zero data)

    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 s...
  • Anonymous's avatar
    Anonymous
    9 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
        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.