=weeknum(today()) needs to be 1 in 2021

[Anonymous]

Currently, PBI Desktop sees the 4th of January as week 2, while I need it to be week 1. I know I can adjust this in the formula, but unfortunately thats not a solution I can use with how this affects all the reports. Is there a way to set 1-3 of Jan as week 0?

[v-cazheng-msft]

Hi, @Anonymous 

You can try the following two Calculated column.

ProductionWeek = if(MONTH('calendar'[Date])=1&&DAY('calendar'[Date])<=3,0,WEEKNUM('calendar'[Date])-1)

ISOWeeknum =

IF(

        'calendar'[ProductionWeek]=0,

        CALCULATE(

            MAX('calendar'[ProductionWeek]),

            FILTER('calendar',

            MONTH([Date])=12&&DAY([Date])&&'calendar'[Year]=EARLIER('calendar'[Year]))

            )+1,

        'calendar'[ProductionWeek]

 )

The result looks like this:

Best Regards,

Caiyun Zheng

Do these solutions work for you? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Everything is linked based on date, so that is not an issue. But how do I swap out WEEKNUM for my Productionweek column? 

For example I have this:

IF('general dim_Date'[ProductionWeek] = WEEKNUM(TODAY())

Changing WEEKNUM to ProductionWeek just gives me an error.

[AilleryO]

Hi,

Since yor needs are not "standard", you have to create a column with your production weeknum (you have it already).

There is no way to change the weeknum in Windows to switch to ISO weeknum.Iin Excel you have both functions but not in PBI.

To use it, do you have a relationship btwn your date table and fact table ? based on date ?

If yes you can use your weeknumbers in your visual and filters.

[Anonymous]

I am using a date table, which has the correct weeks. However, the PBI Desktop formula ignores these weeks, which disrupts my formula's. 

My company uses 2 deviations from normal weeks; we start the week on sunday and we use week 0. I can make PBI start on Sunday by changing Windows settings. Can I do the same for weeknumbers? Or how can I change =weeknum(today()) to make it ISOweeknum by referring to my productionweek column + using today()?

[AilleryO]

Hi again,

Just saw the answer of @amitchandak and it's perfect, if you want your wekknum to be 0, but if you want to have the "correct" weeknum (the ISO one), then it should be in week 53 but week 0 does not exist.

For 2021, the 1st, 2nd and 3rd january are in week 53, and the first day of week 1 is the 4th of january.

It all depends on your needs.

Tell us what is you prefered solution

[AilleryO · Accepted solution]

Hi TKA,

PBI Desktop will give you weeknumber as they are calculated in the US, hence the weeknumber=2 istead of 1. In many countries people are using the weeknumber ISO. Excel offers you both calculation (WEKKNUM and ISOWEEKNUM) but not PBI.

If you are using a date table (which is highly recommended), you ca add in your date table the correct calcultation.

Please find hereunder the details of the calculation in M Code.

//Calculate ISO Weeknum in 4 steps
//1. Calculate the date of jeudi-Thursday in the week
InsertCurrThursday = Table.AddColumn(InsertMonthEnding, "CurrThursday", each Date.AddDays([DateRef], -Date.DayOfWeek([DateRef],1) + 3), type date),
//2. Calculate the 1st january of date (cf step 1)
InsertFirstJan = Table.AddColumn(InsertCurrThursday, "FirstJan", each
        #date(Date.Year([CurrThursday]),1,1),type date),
//3. Calculate the number of days btwn 1st january and the 1st thursday (cf step 1)
InsertDuration= Table.AddColumn(InsertFirstJan, "Duration", each
        Duration.Days(Duration.From([CurrThursday] - [FirstJan])), type number),
//4. Divide the number of days from step 3 by 7, round up and add 1
InsertISOWeekNum = Table.AddColumn(InsertDuration, "NumSemISO", each Number.RoundDown([Duration]/7)+1),
ChangeType5=Table.TransformColumnTypes(InsertISOWeekNum,{{"NumSemISO", Int64.Type}}),
//Delete unusefull columns
RemovedColumns = Table.RemoveColumns(ChangeType5, {"CurrThursday","FirstJan","Duration"})

Your date table must have a column called DateRef (with all dates), otherwise make change accordingly to your situation. 

Hope it helps

[amitchandak]

@Anonymous , if there a generic logic?

example

if(year([date]) = 2021, [Week num]-1, [week num])

refer these columns

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week num= WEEKNUM([Date],2) // 2 mean Monday week  , WEEKNUM([Date],1) //Sunday week

or  have formula based start of the date

if(Weeknum(date(Year([Year]),1,1)) <=3, [Week num]-1, [week num])