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

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