Forum Discussion

Rigolleto's avatar
Rigolleto
Icon for Resolver I rankResolver I
6 years ago
Solved

Calculate Month/Quarter base on Year and week Number

Hi,

 

I need some advice about to create two columns, I am wondering if this could be a Measure or calculated column, here is some explanation about the topic:

 

I have two columns in my query, YEAR and WEEK number, Do I need is two create two new column to show:

 

1.  Months, using Year and Week column as source value to create a new column with the months, eg:

YEAR= 2019/WEEK = 1 == MONTH = JANUARY

2. Quarter, using Year and Week columns as source values to create a new column with the Quarters, eg:

YEAR= 2019/WEEK = 1 == QUARTER= 1

 

Any suggestion about code

 

Thanks In advance

 

Tips and Tricks
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi Rigolleto ,

     

    you should do this by using calculated columns, not measures. It is possible to do this in Power Query, here is how to do it with DAX.

    First create column with the date of the start of the week:

    WeekStartDate = 
var _first7daysOfYear = GENERATESERIES(date('Table'[year];1;1);DATE('Table'[year];1;7))
var _first7Weekdays = ADDCOLUMNS(_first7daysOfYear;"weekday";WEEKDAY([Value];2);"weeknum";WEEKNUM([Value];2))
var _firstMondayDate = SUMMARIZE(filter(_first7Weekdays;[weekday]=1);[Value])
var _firstMondayWeek = SUMMARIZE(filter(_first7Weekdays;[weekday]=1);[weeknum])
return
SWITCH(
    true();
    _firstMondayWeek=2 && 'Table'[week]>=2;_firstMondayDate+(7*('Table'[week]-2));
    _firstMondayWeek=2 && 'Table'[week]=1;DATE(2019;1;1);
    _firstMondayWeek=1;_firstMondayDate+(7*('Table'[week]-1));
    BLANK())

    This code sets the start date of week 1 to 01.01.2019. For the other weeks it sets the start date to the mondat of that week.

     

    Then use this to find the month:

    Month = STARTOFMONTH('Table'[WeekStartDate])

    and quater

    Quater = "Q" & FORMAT(STARTOFQUARTER('Table'[WeekStartDate]); "Q-yyyy")

     

    Keep in mind that this code does not take into account that some years will start with week 53. Also, as most month changes occurs within a week, in this code only the start of the week is used to determine if the week belongs in a month or quater.

    Cheers,
    Sturla


    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

1 Reply

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi Rigolleto ,

     

    you should do this by using calculated columns, not measures. It is possible to do this in Power Query, here is how to do it with DAX.

    First create column with the date of the start of the week:

    WeekStartDate = 
var _first7daysOfYear = GENERATESERIES(date('Table'[year];1;1);DATE('Table'[year];1;7))
var _first7Weekdays = ADDCOLUMNS(_first7daysOfYear;"weekday";WEEKDAY([Value];2);"weeknum";WEEKNUM([Value];2))
var _firstMondayDate = SUMMARIZE(filter(_first7Weekdays;[weekday]=1);[Value])
var _firstMondayWeek = SUMMARIZE(filter(_first7Weekdays;[weekday]=1);[weeknum])
return
SWITCH(
    true();
    _firstMondayWeek=2 && 'Table'[week]>=2;_firstMondayDate+(7*('Table'[week]-2));
    _firstMondayWeek=2 && 'Table'[week]=1;DATE(2019;1;1);
    _firstMondayWeek=1;_firstMondayDate+(7*('Table'[week]-1));
    BLANK())

    This code sets the start date of week 1 to 01.01.2019. For the other weeks it sets the start date to the mondat of that week.

     

    Then use this to find the month:

    Month = STARTOFMONTH('Table'[WeekStartDate])

    and quater

    Quater = "Q" & FORMAT(STARTOFQUARTER('Table'[WeekStartDate]); "Q-yyyy")

     

    Keep in mind that this code does not take into account that some years will start with week 53. Also, as most month changes occurs within a week, in this code only the start of the week is used to determine if the week belongs in a month or quater.

    Cheers,
    Sturla


    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.