Dynamic Calendar questions

[Anonymous]

Hi everyone!

I have some doubts to create a dynamic calendar from my data.

- Is it possible to create a Master calendar only with year and week of the year?

- How could I create a column with the "month" using the week of the year?

My data are like that:

Thanks!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Would you please elaborate what does Dynamic Calendar mean? 

According to the two question, I created a date table via below steps. Please review it to see whether it is acceptable.

Create a new table named as Master Calendar from your source data table.

Master Calendar=
SELECTCOLUMNS ( 'Data Table', "Year", 'Data Table'[Year], "Week", 'Data Table'[Week] )

Create a date table with continual date values. And add three calculated columns [Year Column], [Month Column] and [Week Column]. 

Dim Table =
CALENDAR ( DATE ( 2007, 1, 1 ), DATE ( 2008, 12, 31 ) )

Year Column = 'Dim Table'[Date].[Year]
Month Column = 'Dim Table'[Date].[MonthNo]
Week Column = WEEKNUM('Dim Table'[Date])

Summarize the above table 'Dim Table'.

Dim Table 2 =
SUMMARIZE (
    'Dim Table',
    'Dim Table'[Year Column],
    'Dim Table'[Week Column],
    "Month Column", LASTNONBLANK ( 'Dim Table'[Month Column], 1 )
)

Then, in Master Calendar table, create a calculated column to display Month value.

Month =
LOOKUPVALUE (
    'Dim Table 2'[Month Column],
    'Dim Table 2'[Year Column], 'Master Calendar'[Year],
    'Dim Table 2'[Week Column], 'Master Calendar'[Week]
)

If I have something misunderstood, please correct me and provide more detailed description.

Best regards,

Yuliana Gu

[Anonymous]

Thanks for your help @v-yulgu-msft,

I have probed your solution and works fine.

When I said Dynamic Calendar I refer to a table that depend of my data. Starting with the oldest date of my data and finishing with the most recent.