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?
Best answer by Issam Moalla
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)
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.
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.
