cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erdinch
Helper I
Helper I

how to adjust week number in date dale

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:

 

weekNo.JPG 

 

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

I will appreciate your advice.

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

@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 

 

SU18_powerbi_badge

 

View solution in original post

5 REPLIES 5
AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

 

AlB
Super User III
Super User III

@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 

 

SU18_powerbi_badge

AlB
Super User III
Super User III

@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 

 

SU18_powerbi_badge

 

View solution in original post

amitchandak
Super User IV
Super User IV

@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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors