How to know the month of a week

[Rigoleto]

Hi,

Is there a way to know the month of a week, for instance:

Week                Month

1                         Junuary

26                       Frebuary

I need to resolve the month in order to use it in a chart

Rigo

[v-cherch-msft]

Hi @Rigoleto

You can also create a calendar table and get the week number of Month. Then link the calendar table with week table. You can get the Month with Related Function.

Regards,

Cherie

[Greg_Deckler · Accepted solution]

Here is a surprisingly complex measure to do this:

Week Month = 
VAR __Year = 2018
VAR __Week = MAX(Weeks[Value])
VAR __Calendar = 
    ADDCOLUMNS(
        CALENDAR(DATE(__Year,1,1),DATE(__Year,12,31)),
        "__WeekNum",WEEKNUM([Date]),
        "__Month",MONTH([Date]),
        "__MonthName",FORMAT([Date],"mmmm")
    )
VAR __FilteredCalendar = FILTER(__Calendar,[__WeekNum]=__Week)
VAR __MaxMonth = MAXX(__FilteredCalendar,[__Month]) //switch to MINX for week start month instead of week end month
VAR __FilteredCalendar2 = FILTER(__FilteredCalendar,[__Month]=__MaxMonth)
RETURN MAXX(__FilteredCalendar2,[__MonthName])

You will have to decide what you want to do with weeks that span months. See the comment.