Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Fill in Missing Weeks in Matrix Columns when there is no data

Hi All - I am needing to create a full year Matrix with Month-->Week as columns.  In some cases there may not be data for a given period, but I still need to show ALL of the Months-->Weeks for the year.  I have turned on "Show Items with no data", however it is only showing the Months, not the underlying weeks: 

 

 

I created a custom column in the data source for the date of the week start: 

Date.StartOfWeek([DATE])

 

Then the "Week Number" is a calculated field for the format - 

Week Number = FORMAT('Model'[Date Week],"ww-yyyy")
 
How can I also show the underlying weeks with no data in this instance for the full year?  I don't want add rows for each individual date, as the grain needs to be at the week, and would make the data set in this instance unmanagable. 
In the Matrix columns I have the Month of Start of Week, then the calculated Week Number, I didn't create a hiearchy, and not sure if that's the approach to take.  Thanks! 
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Create a new table(let's call it TableA) with all the Months in one column(Month) and their respective Weeks (in the same format "ww-yyyy") in another column(WeekNumber) . Join this new table TableA with your existing table on Month  column and then use WeekNumber column from TableA in your Matrix visual, instead of using the one in the original table.

    Try this and let me know if this works...

     

    Thanks,

    Sneha

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Create a new table(let's call it TableA) with all the Months in one column(Month) and their respective Weeks (in the same format "ww-yyyy") in another column(WeekNumber) . Join this new table TableA with your existing table on Month  column and then use WeekNumber column from TableA in your Matrix visual, instead of using the one in the original table.

    Try this and let me know if this works...

     

    Thanks,

    Sneha

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      That worked, thank you!