topic Re: Month from Year + week number in DAX Commands and Tips

Month from Year + week number

zenga — Thu, 05 Jan 2023 21:23:57 GMT

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

Re: Month from Year + week number

vicky_ — Thu, 05 Jan 2023 21:55:57 GMT

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.

Re: Month from Year + week number

FreemanZ — Fri, 06 Jan 2023 02:51:01 GMT

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.