cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Creating a date dimension for ISO Week Number and date.

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?

1 ACCEPTED SOLUTION
Frequent Visitor

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)
3 REPLIES 3
Community Support

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

Frequent Visitor

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)
Super User IV

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors