With Analysis Services SP2, I have a time dimension that is organized by year->week->day. The week members show up as 'Week 8', 'Week 10', etc. I like to append each member name with a date to make it more readable, i.e., 'Week 8-2/17' for Feb. 7, which is the Sunday of week 8. I tried the obvious solution (see below), but it doesn't always choose the Sunday of the week. It doesn't choose any day-of-week consistently. Does anyone else face the same issue? Thanks in advance for any suggestions.
'Week ' + rtrim(convert(CHAR, DateName(week, "dbo"."AzDimTime"."ServerTime"
)) + '-' + rtrim(convert(CHAR, DatePart(month, "dbo"."AzDimTime"."ServerTime")) +'/'+rtrim(convert(CHAR, DatePart(day, "dbo"."AzDimTime"."ServerTime"))