Forum Discussion

jnrezk's avatar
jnrezk
Helper III
5 years ago

Calculating Week Start Date from Week Number

Hi there, I have data by week number like this: but I need to add a column that will show me the week end date like this format:   The other complication is that the week numbers are ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

HI jnrezk,

You can use the following calculated column formula to get the start date based on the year and week number:

WeekStart = 
MINX(
    FILTER (
        CALENDAR (
            DATE ( LEFT ( [YearWeekNum], 4 ), 1, 1 ),
            DATE ( LEFT ( [YearWeekNum], 4 ), 12, 31 )
        ),
        WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( [YearWeekNum], 2 ) )
    ),
    [Date]
)

If you are looking for a measure version, you can add a variable to extract the current 'year week number' to calculate:

WeekStart =
VAR cYW =
    MAX ( Table[YearWeekNum] )
RETURN
    MINX (
        FILTER (
            CALENDAR ( DATE ( LEFT ( cYW, 4 ), 1, 1 ), DATE ( LEFT ( cYW, 4 ), 12, 31 ) ),
            WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( cYW, 2 ) )
        ),
        [Date]
    )

Regards,

Xiaoxin Sheng

jnrezk's avatar
jnrezk
Helper III
5 years ago

Hi Im so sorry for the delay. I am trying the column method and having some errors: 

And this was my code how I entered it in:

 

 

 

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    Hey jnrezk ,

     

    the solution provided is based on creating a calculated column using DAX. From the code you provided it's obvious that you are using Power Query / M. This explains why MINX (a DAX function is raising an error).

     

    Create a calculated column using DAX instead of using Power Query.

     

    Regards,

    Tom

    • jnrezk's avatar
      jnrezk
      Helper III
      5 years ago

      Sorry for the back and forth - the code worked but I need it to be for monday start so I changed the code under week Num to 2. but the data isn't correct. 

      WeekStart =
      MINX(
      FILTER (
      CALENDAR (
      DATE ( LEFT ( [ISO Week of ISO Year], 4 ), 1, 1 ),
      DATE ( LEFT ( [ISO Week of ISO Year], 4 ), 12, 31 )
      ),
      WEEKNUM ( [Date], 2 ) = VALUE ( RIGHT ( [ISO Week of ISO Year], 2 ) )
      ),
      [Date]

      For example the June 2nd date shows up for 24 May and it should show up as June 2

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        HI jnrezk,

        The options parameter of weeknum function will change the week number calculation logic. If you want to keep the regular work schedule(Sunday to Saturday) and Monday as result, you can add a '+1' offset to my formulas to change the results.

        WeekStart =
VAR cYW =
    MAX ( Table[YearWeekNum] )
RETURN
    MINX (
        FILTER (
            CALENDAR ( DATE ( LEFT ( cYW, 4 ), 1, 1 ), DATE ( LEFT ( cYW, 4 ), 12, 31 ) ),
            WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( cYW, 2 ) )
        ),
        [Date] + 1
    )

        Regards,
        Xiaoxin Sheng