Skip to main content

Hi! 

 

I am trying to setup my report table to show Week # of the Year. I assume Week # needs to be created in the Week Dimension. Could you help me with the formula? I would like Monday-based weeks. 

 



​​​​

 My final goal is to add Week # before Week property in the table.  

 

Example

Thank you!

@Yaejis There is a column named Week of Year with the formula shown as below. You can create a new column for Week # of the year Or reference ID from dimension table ‘Week of Year’ directly.

 

 


I tried your formula but doesn’t work well: 

MATCH(ROUNDDOWN((DAYS(DATE(YEAR(Week.'Start Date' + MOD(8 - WEEKDAY(Week.'Start Date'), 7)+3),1,1),Week.'Start Date' + MOD(8 - Weekday (Week.'Start Date'),7)+3))/7) +1), 'Week of Year'.Id)

 

 


@Yaejis Can you try this?

ROUNDDOWN((DAYS(DATE(YEAR(Week.'Start Date' + MOD(8 - WEEKDAY(Week.'Start Date'), 7)+3),1,1),Week.'Start Date' + MOD(8 - WEEKDAY(Week.'Start Date'), 7)+3))/7) + 1


Seems it worked! Thank you!

 

I have one last follow up question. Do you know what would be the best approach to show Week of Year and Week Date next to each other? So two columns..  I think the Week of Year should be in Dimension .. 

 


@Yaejis You can convert the week number to the dimension Week of Year using function ITEM. 


ITEM(Week.’week of year’, 'Week of Year'.Id)


Thanks!!


Reply