Displaying all weeks in Matrix columns

[SJ25]

Hello All,

I have created a matrix with week numbers as columns and count of orders as values. I have some weeks where no orders have come in, but the matrix is only displaying week numbers where there are some orders. Is there a way to display all week numbers if if there's no orders that week?

Thanks

[v-diye-msft]

Hi @SJ25 

Similar to the post I encountered the other day:

https://community.powerbi.com/t5/Desktop/Show-all-columns-for-aging-report/m-p/1080466#M500394 

Add a calendar table and manage the relationship, then use the calendar date instead of the original date.

[SJ25]

@v-diye-msft  & @kentyler I generated a calendar table using the following formula:

Calendar Table = CALENDAR(DATE(2020,01,01),DATE(2020,12,31)) and added a week column in that table, so each date has a week number associated with it. Then I created a relationship between the other week column and the one in calendar table (many to many), however my matrix is still skipping some week numbers (image below)

Anything that I'm missing?

UPDATE: In order to get all weeks, after creating a calendar table I had to create another table with just the unique weeknum values and have a one to many relationship with the other week column

[kentyler]

If you add a Calender Table, then that table will contain records for all weeks, regardless of whether they held orders or not.

You can use the week number column from the calendar table to supply the list of weeks for your visual, so it will display weeks, even if they have no orders.