how to get a week comparative week number column?

[Anonymous]

Hi, as there is no week number in date hierarchy, and I don't know how to create a comparative week number according a date column,

the first day of a week is Monday, and all the days' week num  equils week num of TODAY  is always marked as W,

for example, today is 2022-12-29, so the value of  2022-12-26 to 2022-12-30 should be W, and 2022-12-19 to 2022-12-25 should be W-1

and if today is 2022-12-20, then 2022-12-19 to 2022-12-25 should be W, 2022-12-26 to 2022-12-30 should be W+1

do you have any ideas to solve it?

[FreemanZ · Accepted solution]

hi @Anonymous 

Try to add a column like this:

Result = 

VAR _w = WEEKNUM(TODAY(), 2)

VAR _w1 = WEEKNUM([Date], 2)；

VAR _gap =_w1 - _w

RETURN

IF(

    _w=_w1, 

    _w, 

    IF(_gap>0, "W+"&_gap, "W-"&ABS(_gap)

)

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to generate a table like this:

You can click "New Table" and enter this:

Table = var _t = ADDCOLUMNS( CALENDAR(DATE(2022,1,1),DATE(2023,1,31)) , "Year_week",year([Date]) * 100 + weeknum([Date])    )
var _today_week  =year(TODAY()) * 100 + weeknum(TODAY())
var _t2  =ADDCOLUMNS(_t , "Week" , IF(_today_week = [Year_week] , "W" ,IF([Year_week]<_today_week,     
var _cur_week = [Year_week] var _preNum=COUNTROWS(DISTINCT(SELECTCOLUMNS( FILTER(_t ,[Year_week]>= _cur_week && [Year_week]<_today_week),"Year_week",[Year_week]))) return
"W-"& _preNum ,
 var _cur_week = [Year_week] var _afterNum = COUNTROWS(DISTINCT(SELECTCOLUMNS( FILTER(_t ,[Year_week]>_today_week&&[Year_week]<=_cur_week),"Year_week",[Year_week]))) return
 "W+"&_afterNum )     ))
return
_t2 

Then we can meet your need.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[FreemanZ · Accepted solution]

hi @Anonymous 

Try to add a column like this:

Result = 

VAR _w = WEEKNUM(TODAY(), 2)

VAR _w1 = WEEKNUM([Date], 2)；

VAR _gap =_w1 - _w

RETURN

IF(

    _w=_w1, 

    _w, 

    IF(_gap>0, "W+"&_gap, "W-"&ABS(_gap)

)

[themistoklis]

@Anonymous 

Have you tried creating a YearWeek column to do your calculations:

Using Dax the ISO YearWeek formula is the following:

YearWeek = IF(WEEKNUM('Calendar'[Date], 21)=53 && MONTH('Calendar'[Date])=1, YEAR('Calendar'[Date]) -1, YEAR('Calendar'[Date])) & "-" & WEEKNUM('Calendar'[Date], 21)

Otherwise you can create a Calendar using Power Query which will have the ISO Week number and ISO Year Week:

let

    Source = List.Dates,
    #"Invoke function" = Source(#date(2019, 11, 01), Number.From(#date(2021, 03, 31) - #date(2019, 11, 01))+1, #duration(1, 0, 0, 0)),
  
    #"List to table" = Table.FromList(#"Invoke function", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    Date = Table.RenameColumns(#"List to table",{{"Column1", "Date"}}),
    #"Day Added" = Table.AddColumn(Date, "Day", each Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),
  
    #"Day Name Added" = Table.AddColumn(#"Day Added", "Day Name", each Date.ToText([Date],"dddd","en-US")),
    #"Day of Week No Added - ISO Monday" = Table.AddColumn(#"Day Name Added", "Day of Week No", each Date.DayOfWeek([Date], Day.Monday)),
    #"Month No Added" = Table.AddColumn(#"Day of Week No Added - ISO Monday", "Month No", each Date.Month([Date])),

    #"Month Name Added" = Table.AddColumn(#"Month No Added", "Month Name", each Date.ToText([Date],"MMMM","en-us")),

    #"Quarter No Added" = Table.AddColumn(#"Month Name Added", "Quarter No", each Date.QuarterOfYear([Date])),
    #"Semester No Added" = Table.AddColumn(#"Quarter No Added", "Semester No", each if [Quarter No] <= 2 then "1" else "2" ),

    #"EUROPEAN Week No added" = Table.AddColumn(#"Semester No Added", "WeekNo", each Text.PadStart(Number.ToText(Date.WeekOfYear([Date])+1),2,"0")),

    #"ISO Week Number added" = Table.AddColumn(#"EUROPEAN Week No added", "ISO_WeekNo", each if
    Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
    then 
    Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
    else if
    (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53
    and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
    then 1
    else 
    Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)),
    #"ISO Year added" = Table.AddColumn(#"ISO Week Number added", "ISO_Year", each if [ISO_WeekNo] = 53 and [Month No] = 1 then Date.Year([Date])-1 else if [ISO_WeekNo] = 1 and [Month No] = 12 then Date.Year([Date])+1  else Date.Year([Date])),
    #"Changed Type" = Table.TransformColumnTypes(#"ISO Year added",{ {"Date", type date}, {"Month No", type text}, {"Day", type text}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter No", type text}, {"WeekNo", type text}, {"ISO_WeekNo", type text}, {"ISO_Year", type text}, {"Day of Week No", Int64.Type}}),

    #"Year Added" = Table.AddColumn(#"Changed Type", "Year", each Number.ToText(Date.Year([Date]))),

    #"Year Month Added" = Table.AddColumn(#"Year Added", "Year-Month", each [Year] & "-" & Text.End("00" & [Month No], 2)),
    #"Year Week Added" = Table.AddColumn(#"Year Month Added", "Year-Week", each [ISO_Year] & "-" & Text.End("00" & [ISO_WeekNo], 2)),

    #"Year Quarter Added" = Table.AddColumn(#"Year Week Added", "Year-Quarter", each [Year] & "Q"& [Quarter No]),
    #"Year Semester Added" = Table.AddColumn(#"Year Quarter Added", "Year-Semester", each [Year] & "S" & [Semester No]),

    #"Sort Day" = Table.AddColumn(#"Year Semester Added", "SortDay", each Date.Day([Date])),

    #"Sort DayName" = Table.AddColumn(#"Sort Day", "SortDayName", each Date.DayOfWeek([Date],1)),

    #"Sort Week No" = Table.AddColumn(#"Sort DayName", "SortWeekNo", each Number.FromText([ISO_WeekNo])),

    #"Sort YearMonth" = Table.AddColumn(#"Sort Week No", "SortYearMonth", each [Year]&Text.PadStart([Month No],2,"0")),

    #"Sort YearQuarter" = Table.AddColumn(#"Sort YearMonth", "SortYearQuarter", each [Year]&Text.PadStart([Quarter No],2,"0")),

    #"Changed Type to NO" = Table.TransformColumnTypes(#"Sort YearQuarter",{{"SortYearMonth", Int64.Type}, {"SortYearQuarter", Int64.Type}, {"SortDayName", Int64.Type}, {"SortDay", Int64.Type}, {"SortWeekNo", Int64.Type}, {"Year", Int64.Type}, {"ISO_Year", Int64.Type}})
in
    #"Changed Type to NO"