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!
Best answer by Min Li Bright Point
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
+5
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)
+5
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 ..
+5
ITEM(Week.’week of year’, 'Week of Year'.Id)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
