cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Custom Column on Week

hi all ,

can anyone help me with the code ? in Year 2021 is the different between the Past Year.

as i calculate Week Number I used the Code : if Date.WeekOfYear([Date],6)>52 then 52
else Date.WeekOfYear([Date],6) ,

Though i wanna set a condition , If year 2021 , the day start from 01/01/2021 to 01/08/2021  - 1   else it will run as normal as the formula is showing and then it will automatic running week by week. While Currently 01/01/2021 - it falls on Week 1 .

Below is my Mcode for my company calendar :

let
Source = {Number.From(#date(2010,1,1))..Number.From(#date(2021,12,31))},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Added Custom3",{{"Date", Order.Descending}}),
#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Week Number", each if Date.WeekOfYear([Date],6)>52 then 52
else Date.WeekOfYear([Date],6)),
else if [Week Number]>4 and [Week Number]<9 then 2
else if [Week Number]>8 and [Week Number]<14 then 3
else if [Week Number]>13 and [Week Number]<18 then 4
else if [Week Number]>17 and [Week Number]<22 then 5
else if [Week Number]>21 and [Week Number]<27 then 6
else if [Week Number]>26 and [Week Number]<31 then 7
else if [Week Number]>30 and [Week Number]<35 then 8
else if [Week Number]>34 and [Week Number]<40 then 9
else if [Week Number]>39 and [Week Number]<44 then 10
else if [Week Number]>43 and [Week Number]<48 then 11
else if [Week Number]>47 and [Week Number]<53 then 12
else 0),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Year", Int64.Type}, {"Month No", Int64.Type}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type", "Month", each Date.ToText(DateTime.Date(#datetime([Year], [Month No], 1, 00, 00, 00)),"MMM")),
else if [Month No]>=4 and [Week Number]<7 then 2
else if [Month No]>=7 and [Month No]<10 then 3
else if [Month No]>=10 and [Month No]<13 then 4
else 2),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom6",{{"Week Number", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom7",{{"Custom", "Week/Month"}})
in
#"Renamed Columns1"

1 ACCEPTED SOLUTION
Super User

Update it as follows:

``````#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Week Number", each if Date.WeekOfYear([Date],6)>52 then 52
else if ([Year]=2021 and List.Contains({1,2}, Date.WeekOfYear([Date],6))) then 1
esle if [Year]=2021 then (Date.WeekOfYear([Date],6)-1) else Date.WeekOfYear([Date],6))``````

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

9 REPLIES 9
Super User

Update it as follows:

``````#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Week Number", each if Date.WeekOfYear([Date],6)>52 then 52
else if ([Year]=2021 and List.Contains({1,2}, Date.WeekOfYear([Date],6))) then 1
esle if [Year]=2021 then (Date.WeekOfYear([Date],6)-1) else Date.WeekOfYear([Date],6))``````

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

Post Prodigy

@v-stephen-msft  , hi , new week number show 53 , it is not right dear.

we have only 52 weeks , can we do it on Mcode rather calculated column ?

Community Support

You could try two calculated column.

``````Week Num =
WEEKNUM ( [Date] + 1, 1 )``````
``````New Week Num =
IF (
[Date] <= DATE ( 2021, 1, 8 )
&& [Date] >= DATE ( 2021, 1, 1 ),
1,
IF (
[Date] > DATE ( 2021, 1, 8 )
&& [Date] <= DATE ( 2021, 12, 31 ),
[Week Num] - 1,
[Week Num]
)
)``````

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

So what is the expected result in the pic above?? I don't understand the requirement. Please detail the week number that you want for each of those days (and others if relevant)

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

Post Prodigy

hi @AlB , as i circle it show the number it is "Week" , below is the formula you recommend to fix on 01/01/2021-01/08/2021 is the Week 1  , and we have fixed that , but on 01/09/2021-01/15/2021 it show Week 3 , but it should be Week 2 ,

Super User

You need to modify this step:

``````#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Week Number", each if Date.WeekOfYear([Date],6)>52 then 52
else if ([Year]=2021 and List.Contains({1,2}, Date.WeekOfYear([Date],6))) then 1
else Date.WeekOfYear([Date],6))``````

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

Post Prodigy

hi  @AlB @amitchandak  it's turning to Week 1 , and it should be runs in  following Week , by Week 2 and Week 3 Week 4 Week 5.....

because right now it didn't have Week 2 .
Can you help on this .So Excited, We almost there.

thanks you so much

Super User

@Chanleakna123 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if these can help

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

Post Prodigy

https://www.dropbox.com/s/flw231yuqes4048/445%20Calendar.pbix?dl=0

above is 445 Calendar , as i have Own calendar , though all Calendar based on the calculation , those turn out right .
But because in Year 2021 is different i have the issue with Week 1 :

****i wanna set a condition , If year 2021 , the day start from 01/01/2021 to 01/08/2021  - Will be in Week 1  , and then all the date are running in following that Week 1 , Week 2 , Week 3 etc...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.