Month from Year + week number

[zenga]

People, 

My fact table have no Date column , i just have the Year and Week number columns,
there any way to use DAX to dicovery the month of this combination (Year + week number) ?

I know, some times we have a week num existing in 2 months... but if is possible to choose the month when de weekstarts, could be good...

Thanks

[vicky_ · Accepted solution]

The easiest way would be to just create a new date table using CALENDARAUTO() and add a column for weeks, then join the two / create a relationship between them. If you're worried about one week having two months, take a look at the ISO Weeks functions here, which should help you pick the starting day for each week.  

[FreemanZ · Accepted solution]

hi @zenga 

you would

1) need a DataTable to help you, e.g. like this:

DateYearWeekNumberMonth

1/31/2022	2022	6	1
2/1/2022	2022	6	2
4/30/2022	2022	18	4
5/1/2022	2022	19	5

2) add a column in your FactTable like this:

Month = 
VAR _year = [Year]
VAR _wn = [WeekNumber]
RETURN
MINX(
    FILTER(
        DateTable,
        DateTable[Year] = _year
            &&DateTable[WeekNumber] =_wn
    ),
    DateTable[Month]
)

i tried an it worked like this:

BTW, if you can, for date calculation, try always to have a date column in fact table connected to a decent date table. 

[vicky_ · Accepted solution]

The easiest way would be to just create a new date table using CALENDARAUTO() and add a column for weeks, then join the two / create a relationship between them. If you're worried about one week having two months, take a look at the ISO Weeks functions here, which should help you pick the starting day for each week.