How to display different scale in one matrix visual

[Anonymous]

Hi,

I have a table which contains week and date (not always the starting day of the week) as below

ID	week	date
001	W428	7/10
002	W429	7/15
003	W430	7/24
004	W431	7/29

And I have created a matrix, ID as row, week as column, count of ID as value.

ID	W428	W429	W430	W431
001	1
002		1
003			1
004				1

Now I want the matrix to display part of weeks, and the last week show in date form

ID	W428	W429	W430	7/28	7/29	7/30	7/31	8/1	8/2	8/3
001
...

Is it possible to do? Any answer will be helpful!

[bhanu_gautam · Accepted solution]

@Anonymous , First create a date table

DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2022, 7, 1), DATE(2022, 8, 3)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Week", WEEKNUM([Date], 2) -- Week starts on Monday
)

Create relationship with your table using date column

Then Create a matrix visual , Drag ID column to Rows, Week column form main table to Columns and ID column to values and set it to count

Drag the Date column from the DateTable to the Columns field in the matrix visual.
Ensure that the Date column is placed after the Week column in the Columns field.