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


Ask your question here!

Terms & ConditionsCookie settings