Creating a date dimension for ISO Week Number and date.

[Ravi_621]

I have a table where I only have weekdays and weeknumbers. The weeknumbers are based on ISO 1806 (weeknum(date,21)).

I have transposed the table to create two columns and added the weeknumber.

I have a date dimension with Year Week WeekDay and would like to transform this to standard date.

The Year and week is based on ISO 1806, week is based on (Weeknum(date,21) and Weekday(date,2).

I have also created a column in the date dimension:

"ISOYear",IF(WEEKNUM ( [Date],21 ) < 5 && WEEKNUM ( [Date],2 ) > 50, YEAR( [Date] ) +1 , IF(WEEKNUM ( [Date],21 )>50 && WEEKNUM ( [Date],2 <5 ), YEAR( [Date] )-1, YEAR( [Date])))

 "WeekDayOfWeek",IF (WEEKNUM ( [Date] ) < 10, "0", "") & WEEKNUM([Date]) & "-" & WEEKDAY ( [Date] , 2),

I have tried this to make an connection with the standard date but the ISO 1806 does not work correctly.

Can someone help me?

[Icey]

Hi @Ravi_621 ,

Glad to hear that. Please accept your reply as a solution so that people who may have the same question can get the solution directly. Your contribution is highly appreciated.

Best regards

Icey

[Ravi_621 · Accepted solution]

I have solved this by using the Column, Date start of the week.
By using the Date of the week I can transform the Year Week WeekDay to Standard date and create a relationship to my date table.

StandardDate = TLR_TIJDLIJST_UREN[DatumVan] + (RIGHT(TLR_TIJDLIJST_UREN[Attribute],1)-1)

[amitchandak]

@Ravi_621 , Try like

Week to Date = var _st = date(left([Week],4),1,1)
var _day = right([Week],1)
var _week = mid([Week],6,2)*1
Return _st+((_week-1)*7) -WEEKDAY(_st,2)+1 +_day

or

Week to Date = var _st = date(left([Week],4),1,1)
var _day = right([Week],1)
var _week = mid([Week],6,1)*1
Return _st+((_week-1)*7) -WEEKDAY(_st,2)+1 +_day //sunday week