Custom Column on Week

[Chanleakna123]

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}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Week Day Name", each Date.ToText([Date], "dddd")),
#"Added Custom3" = Table.AddColumn(#"Added Custom", "Year", each Date.Year([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)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month No", each if [Week Number]>0 and [Week Number]<5 then 1
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")),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Month Year", each Date.ToText(DateTime.Date(#datetime([Year], [Month No], 1, 00, 00, 00)),"MMM-yyyy")),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Quaters", each if [Month No]>=1 and [Month No]<4 then 1
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}}),
#"Added Custom7" = Table.AddColumn(#"Changed Type2", "Custom", each Text.Combine({[Month]," ","W",[Week Number]})),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom7",{{"Custom", "Week/Month"}})
in
#"Renamed Columns1"

[AlB · Accepted solution]

@Chanleakna123 

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 

[Chanleakna123]

@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 ?

[v-stephen-msft]

Hi @Chanleakna123 ,

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.

[AlB]

@Chanleakna123 

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 

[Chanleakna123]

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 , 

[AlB]

Hi @Chanleakna123 

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 

[Chanleakna123]

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

[amitchandak]

@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...

Week That Resets Yearly
https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3...

[Chanleakna123]

hi @amitchandak  

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...