Forum Discussion
Build calendar table with fixed weeknumbers assigned to months, quarters etc.
Hi Anonymous ,
Seems that there are some issues in your if statement.
Like "if [WeekNb] >= 6 or [WeekNb] <=9 then 2", the logic should be "and" not "or", this is why everything else was tagged with 2.
Try to modify the formula like this:
= Table.AddColumn( #"Changed Type", "NT-MonthNb", each if [WeekNb] >= 53 and [MonthNb] = 1 or [WeekNb] <= 5 then 1 else if [WeekNb] >= 6 and [WeekNb] <= 9 then 2 else if [WeekNb] >= 10 and [WeekNb] <= 13 then 3 else if [WeekNb] >= 14 and [WeekNb] <= 18 then 4 else if [WeekNb] >= 19 and [WeekNb] <= 22 then 5 else if [WeekNb] >= 23 and [WeekNb] <= 26 then 6 else if [WeekNb] >= 27 and [WeekNb] <= 31 then 7 else if [WeekNb] >= 32 and [WeekNb] <= 35 then 8 else if [WeekNb] >= 36 and [WeekNb] <= 39 then 9 else if [WeekNb] >= 40 and [WeekNb] <= 44 then 10 else if [WeekNb] >= 45 and [WeekNb] <= 48 then 11 else if [WeekNb] >= 49 and [WeekNb] <= 53 or [MonthNb] < 1 then 12 else null, Int64.Type)Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Jimmy801
Our financial year follows the ISO calandar and starts at January 1st. So in 2021 the first days of the year is week 53. The problem is not on week level, beacause week 1 is alway compared to week 1, no mattere the difference in dates.
The challenge occours when I look at the data per month, quarter or tertial. January is always week 1-5, even if week five is in February on the ISO calendar. I tried to use a nested IF statement but this only gives the correct week numbers for January, everything else was tagged with 2. Not sure why though.
#"Added Calendar NT-MonthNb" = Table.AddColumn(#"Added MonthYear", "NT-MonthNb", each
if [WeekNb] >=53 and [MonthNb] = 1 or [WeekNb] <=5 then 1 else
if [WeekNb] >= 6 or [WeekNb] <=9 then 2 else
if [WeekNb] >= 10 or [WeekNb] <= 13 then 3 else
if [WeekNb] >= 14 or [WeekNb] <= 18 then 4 else
if [WeekNb] >= 19 or [WeekNb] <= 22 then 5 else
if [WeekNb] >= 23 or [WeekNb] <= 26 then 6 else
if [WeekNb] >= 27 or [WeekNb] <= 31 then 7 else
if [WeekNb] >= 32 or [WeekNb] <= 35 then 8 else
if [WeekNb] >= 36 or [WeekNb] <= 39 then 9 else
if [WeekNb] >= 40 or [WeekNb] <= 44 then 10 else
if [WeekNb] >= 45 or [WeekNb] <= 48 then 11 else
if [WeekNb] >= 49 or [WeekNb] <= 53 and [MonthNb] < 1 then 12 else null, Int64.Type),
Community SupportHi Anonymous ,
Seems that there are some issues in your if statement.
Like "if [WeekNb] >= 6 or [WeekNb] <=9 then 2", the logic should be "and" not "or", this is why everything else was tagged with 2.
Try to modify the formula like this:
= Table.AddColumn(
#"Changed Type", "NT-MonthNb", each
if [WeekNb] >= 53 and [MonthNb] = 1 or [WeekNb] <= 5 then 1 else
if [WeekNb] >= 6 and [WeekNb] <= 9 then 2 else
if [WeekNb] >= 10 and [WeekNb] <= 13 then 3 else
if [WeekNb] >= 14 and [WeekNb] <= 18 then 4 else
if [WeekNb] >= 19 and [WeekNb] <= 22 then 5 else
if [WeekNb] >= 23 and [WeekNb] <= 26 then 6 else
if [WeekNb] >= 27 and [WeekNb] <= 31 then 7 else
if [WeekNb] >= 32 and [WeekNb] <= 35 then 8 else
if [WeekNb] >= 36 and [WeekNb] <= 39 then 9 else
if [WeekNb] >= 40 and [WeekNb] <= 44 then 10 else
if [WeekNb] >= 45 and [WeekNb] <= 48 then 11 else
if [WeekNb] >= 49 and [WeekNb] <= 53 or [MonthNb] < 1 then 12 else null,
Int64.Type)
Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.