cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ravi_621
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)).

 

Ravi_621_3-1603466641868.png

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

Ravi_621_4-1603466814018.png

Ravi_621_5-1603466956206.png

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

 

 

Ravi_621_2-1603466184729.png

 

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

 

Ravi_621_1-1603465774611.png

 

Can someone help me?

1 ACCEPTED SOLUTION
Ravi_621
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.

 

Ravi_621_0-1603709696849.png

 

 

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

View solution in original post

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

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

 

Ravi_621_0-1603709696849.png

 

 

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

View solution in original post

amitchandak
Super User IV
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
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors