how to adjust week number in date dale

[erdinch]

Hello,

I have just extended my date table so it included 2021 dates.

I used that source trigger

= List.Dates(
#date(2020,1,1) ,
365*2 ,
#duration(1,0,0,0)
)

Then I insert new column with week number using:

= Table.AddColumn(#"Changed Type", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type)

The point is that in the border betweem 2020 and 2021 I have:

 

But I would like week number one to start as of 4th of Jan, 2021.

I will appreciate your advice.

[Anonymous]

Please look Function for ISO Year/Week number (ISO 8601)) 

NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)

[AlB]

Hi @erdinch 

See if this can help https://community.powerbi.com/t5/Desktop/Corrigindo-n%C3%BAmero-de-semanas-no-ano/m-p/1580167

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@erdinch 

or have a look at this

https://community.powerbi.com/t5/Desktop/Incorrect-week-number-at-year-end-beginning/m-p/1474033

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@erdinch 

See it all at work in the attached file (based on the link provided earlier)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@erdinch , this is dax code to do that. see you can get that in power query

new column in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

min week start of year = minx(filter('Date',year([Week Start date])=earlier(year(Week Start date]))),[Week Start date])
week No = quotient(datediff([min week start of year],[date],day),7)+1
Week Start Year = minx(filter('Date',[Year] =earlier([Year])),[Week Start date])

[erdinch]

Hello amitchandak,

bear with me I am not so good at power BI.

My date table is named "Date_table".So I tried to add column by clicking ib the three dots (more option) on the right side of the panel where are listed my existing tables. I saw an option "new column". After pressing that I recieve an empty line where I can add the DAX code.  

Drespite of the fact that I replaced  'Date' with  'Date_table' -the code was not accepted and I get some red underlines saying "parameter is not correct"

Can you please explain in more details? PLEASE.