Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Alecsen
Regular Visitor

Problem with WeekNum ( Date Table)

Hello all,

 

Please help me out.

 

I have a big data set that worked perfectly from last year, this year I have one problem with the WeekNum : 

1. I am trying to have the "First week start of the year" 27.12.2021 - Week 53-2021  and after on "First week start of the year" 03.01.2022 - Week 1 2022
2. After that to have the week number on the same value and the YearWeek , but is strange because i get differently results, i tried with ISOweeks and any other tips but i keep get stuck .

 

Here is a sample picture : 

 

Capture.PNG

Any help or suggestions is apreciated, even a replace or IF formula ; 

9 REPLIES 9
KNP
Super User
Super User

Hi @Alecsen - are you able to post the code you used to build this date table? It will make it easier for us to help you.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Alecsen
Regular Visitor

Hello,

 

I have build the data table as a query  :

 

Alecsen_0-1643658265726.png

let fnDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertQuarterNum = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date])),
InsertQuarter = Table.AddColumn(InsertQuarterNum, "Quarter", each "Q" & Number.ToText([Quarter Num])),
InsertMonth = Table.AddColumn(InsertQuarter, "Month Num", each Date.Month([Date]), type text),
InsertStartOfMonth = Table.AddColumn(InsertMonth, "StartOfMonth", each Date.StartOfMonth([Date]), type date),
InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "EndOfMonth", each Date.EndOfMonth([Date]), type date),
InsertDay = Table.AddColumn(InsertEndOfMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year]*10000 + [Month Num]*100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month", each Date.ToText([Date], "MMMM", Culture), type text),
InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month short", each Date.ToText([Date], "MMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertShortMonthName, "Month Year", each [Month short]& " " & Number.ToText([Year]),type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Num", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "Weekday", each Date.ToText([Date], "dddd", Culture), type text),
InsertShortDayName = Table.AddColumn(InsertDayName, "Weekday short", each Date.ToText([Date], "ddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertShortDayName , "EndOfWeek", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Num", each Date.WeekOfYear([Date])),
InsertMonthWeekNumber= Table.AddColumn(InsertWeekNumber, "WeekOfMonth Num", each Date.WeekOfMonth([Date])),
InsertMonthnYear = Table.AddColumn(InsertMonthWeekNumber,"Month-YearOrder", each [Year]*10000 + [Month Num]*100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"Quarter-YearOrder", each [Year]*10000 + [Quarter Num]*100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"Quarter-YearOrder", Int64.Type},{"Week Num", Int64.Type},{"WeekOfMonth Num", Int64.Type},{"Quarter", type text},{"Year", type text},{"Month-YearOrder", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"Month Num", Int64.Type}, {"Quarter Num", Int64.Type}, {"Weekday Num", Int64.Type}})
in
ChangedType1
in
fnDateTable

 

 

I'm not sure I'm understanding 100% but I've edited your function to add ISOWeekNumber and ISOYear.

Let me know if this works...

let
  fnDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
    let
      DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
      Source = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
      TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
      ChangedType = Table.TransformColumnTypes(TableFromList, {{"Column1", type date}}),
      RenamedColumns = Table.RenameColumns(ChangedType, {{"Column1", "Date"}}),
      InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type text),
      InsertQuarterNum = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date])),
      InsertQuarter = Table.AddColumn(
        InsertQuarterNum,
        "Quarter",
        each "Q" & Number.ToText([Quarter Num])
      ),
      InsertMonth = Table.AddColumn(InsertQuarter, "Month Num", each Date.Month([Date]), type text),
      InsertStartOfMonth = Table.AddColumn(
        InsertMonth,
        "StartOfMonth",
        each Date.StartOfMonth([Date]),
        type date
      ),
      InsertEndOfMonth = Table.AddColumn(
        InsertStartOfMonth,
        "EndOfMonth",
        each Date.EndOfMonth([Date]),
        type date
      ),
      InsertDay = Table.AddColumn(InsertEndOfMonth, "DayOfMonth", each Date.Day([Date])),
      InsertDayInt = Table.AddColumn(
        InsertDay,
        "DateInt",
        each [Year] * 10000 + [Month Num] * 100 + [DayOfMonth]
      ),
      InsertMonthName = Table.AddColumn(
        InsertDayInt,
        "Month",
        each Date.ToText([Date], "MMMM", Culture),
        type text
      ),
      InsertShortMonthName = Table.AddColumn(
        InsertMonthName,
        "Month short",
        each Date.ToText([Date], "MMM", Culture),
        type text
      ),
      InsertCalendarMonth = Table.AddColumn(
        InsertShortMonthName,
        "Month Year",
        each [Month short] & " " & Number.ToText([Year]),
        type text
      ),
      InsertCalendarQtr = Table.AddColumn(
        InsertCalendarMonth,
        "Quarter Year",
        each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]),
        type text
      ),
      InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Num", each Date.DayOfWeek([Date])),
      InsertDayName = Table.AddColumn(
        InsertDayWeek,
        "Weekday",
        each Date.ToText([Date], "dddd", Culture),
        type text
      ),
      InsertShortDayName = Table.AddColumn(
        InsertDayName,
        "Weekday short",
        each Date.ToText([Date], "ddd", Culture),
        type text
      ),
      InsertWeekEnding = Table.AddColumn(
        InsertShortDayName,
        "EndOfWeek",
        each Date.EndOfWeek([Date]),
        type date
      ),
      InsertWeekNumber = Table.AddColumn(InsertWeekEnding, "Week Num", each Date.WeekOfYear([Date])),
      InsertISOWeekNumber = Table.AddColumn(
        InsertWeekNumber,
        "ISO Week Number",
        each
          if Number.RoundDown(
            (Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
          )
            = 0
          then
            Number.RoundDown(
              (
                Date.DayOfYear(#date(Date.Year([Date]) - 1, 12, 31))
                  - (Date.DayOfWeek(#date(Date.Year([Date]) - 1, 12, 31), Day.Monday) + 1)
                  + 10
              )
                / 7
            )
          else if (
            Number.RoundDown(
              (Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
            )
              = 53 and (Date.DayOfWeek(#date(Date.Year([Date]), 12, 31), Day.Monday) + 1 < 4)
          )
          then
            1
          else
            Number.RoundDown(
              (Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
            )
      ),
      InsertISOYear = Table.AddColumn(
        InsertISOWeekNumber,
        "ISO Year",
        each Date.Year(Date.AddDays([Date], 26 - [ISO Week Number]))
      ),
      InsertMonthWeekNumber = Table.AddColumn(
        InsertISOYear,
        "WeekOfMonth Num",
        each Date.WeekOfMonth([Date])
      ),
      InsertMonthnYear = Table.AddColumn(
        InsertMonthWeekNumber,
        "Month-YearOrder",
        each [Year] * 10000 + [Month Num] * 100
      ),
      InsertQuarternYear = Table.AddColumn(
        InsertMonthnYear,
        "Quarter-YearOrder",
        each [Year] * 10000 + [Quarter Num] * 100
      ),
      ChangedType1 = Table.TransformColumnTypes(
        InsertQuarternYear,
        {
          {"Quarter-YearOrder", Int64.Type},
          {"Week Num", Int64.Type},
          {"WeekOfMonth Num", Int64.Type},
          {"Quarter", type text},
          {"Year", type text},
          {"Month-YearOrder", Int64.Type},
          {"DateInt", Int64.Type},
          {"DayOfMonth", Int64.Type},
          {"Month Num", Int64.Type},
          {"Quarter Num", Int64.Type},
          {"Weekday Num", Int64.Type}
        }
      )
    in
      ChangedType1
in
  fnDateTable

 

If this is what you're looking for, you'll need to take into account anything you're doing with "year" and consider using "ISO year".

 

For reference:

https://datacornering.com/how-to-calculate-iso-week-number-in-power-query/

https://datacornering.com/how-to-calculate-iso-year-in-power-query/

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
v-rongtiep-msft
Community Support
Community Support

Hi @Alecsen ,

I have create a simple sample. Please refer to see if it helps you.

 

wekkday_ = WEEKDAY('Table'[Date],2)
weeknumber = WEEKNUM('Table'[Date],2)
YearWee = YEAR('Table'[Date])&"-"&'Table'[weeknumber]
_First start week of the year = CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[weeknumber]=SELECTEDVALUE('Table'[weeknumber])))

 

vpollymsft_0-1643177884827.png

If I have misunderstood your meaning, could you provide your pbix file without privacy information and desired output.

 

 

Best Regards

Community Support Team _ Polly

 

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

 

 

Hello @v-rongtiep-msft ,

 

Thank for your help, bassicly is somethin that you describe . But as I can see in the previous report the value days of the week 53 in your screen is 5 days and after the first of the year Week 1 has only one day. 

 

I would like to have week 53 as week 1 bassicly with 7 days .

mahoneypat
Employee
Employee

Please see if this article is helpful.

445 Calendar with 53-Week Years – Hoosier BI

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


KNP
Super User
Super User

@Alecsen,

 

Check out this post...

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

 

I think you'l find what you need in there.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Alecsen
Regular Visitor

@KNP I trying without any help. I have the same results. 

Alecsen
Regular Visitor

@amitchandak any suggestions? 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors