Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Chanleakna123
Post Prodigy
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}}),
#"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"

 

 

 

Capture 1.PNG

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@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 

SU18_powerbi_badge

 

View solution in original post

9 REPLIES 9
AlB
Super User
Super User

@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 

SU18_powerbi_badge

 

Chanleakna123
Post Prodigy
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 ?

v-stephen-msft
Community Support
Community Support

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

32.png

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
Super User
Super User

@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 

 

SU18_powerbi_badge

 

 

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
Super User
Super User

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 

 

SU18_powerbi_badge

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

 

4.PNG

amitchandak
Super User
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...

 

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

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

 

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors