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
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

    • jnrezk's avatar
      jnrezk
      Helper III
      5 years ago

      Hi - sorry no need to add the +1 I found a field that automatically counts the weeks as monday to sunday but the weeks are off as indicated in the image in my other response. I tried your new way with the sunday dates and it didnt solve it either.