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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
Anonymous
Not applicable

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 @Anonymous ,

 

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
Microsoft Employee
Microsoft 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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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