Forum Discussion

milpro011's avatar
milpro011
Icon for Helper I rankHelper 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 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!

 

 

 

  • 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.

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Try clicking 'Show items with no data' on the dropdown for date in the Axis field well

    • milpro011's avatar
      milpro011
      Icon for Helper I rankHelper I

      Anonymous, 

      Thank you for your reply. I have checked off that option, but it doesn't do anything because there are no zero entires in the table to begin with. 

       

      Would you happen to know is the best way to add "items with no data"? 

      Thank you, m.

      • Anonymous's avatar
        Anonymous
        Not applicable

        Are the chart values coming from one table?

         

        Are the data tables linked to a Date dimension table that you are using for your X axis?

  • Hi milpro011 I would suggest adding rows into your data where there was no activity.


    This should cause the dates to display, and when selecting the option to "Show rows with No data" that should then work.


    But without having actual rows with dates, it is working as expected?