Skip to main content

Hi!

I want to add “Weeknumber” to my “Day” dimension.
Since WEEKNUM() is not available, I want to use the equivalent of SHIFT/PREVIOUS to calculate the weeknumber.

I have a dimension called Weeknumber (1-53), and would like to do something along the following line, in order map Days → Weeknumbers:
 

//Check if first day of year, 
IF(
LEFT(Day.'dd-MM-YYYY',5)="01-01" ,
ITEM(1,Weeknumber.Week),

// Else: check if workday = Monday then add 1 to week count
IF(Day.'Day of Week' = 'Day of Week"."Monday",
PREVIOUS(Day.Weeknumber)+1,
//IF NOT Monday, then keep current weeknum
PREVIOUS(Day.Weeknumber))

However, both PREVIOUS and SHIFT does not seem to work. Is there a workaround?

Day Dimension


Hello OyvindB,

Could you try the following formula in the property of the day dimension:

ITEM(TEXT(CUMULATE(IF(Day.'Day of Week'.Name = "Monday", 1), Day,Day.Year)),WEEKNUM)

This formula can be broken down as follows:

  • the CUMULATE function will attribute the week number to each day and resets each Year as it is used as the grouping dimension:
CUMULATE(IF(Day.'Day of Week'.Name = "Monday", 1), Day,Day.Year)

        You can read more about this function and in the CUMULATE function documentation.

  • The ITEM() will retrieve the Weeknumber item based on the unique property in the Weeknumber dimension.
     
  • The TEXT() function was added if the unique property of your Weeknumber dimension has a type TEXT
    This function can be omitted if the unique property of the Weeknumber is number or integer type.
     

Let me know if this helps.

Best,
Issam


Reply