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

         

  • jnrezk's avatar
    jnrezk
    Helper III
    5 years ago

    The source is google analytics and they only provideo ISOWeeknumber which i need as a date format