March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 :
Any help or suggestions is apreciated, even a replace or IF formula ;
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 ;). |
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. | Proud to be a Super User! |
Hello,
I have build the data table as a query :
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 ;). |
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. | Proud to be a Super User! |
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])))
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 .
Please see if this article is helpful.
445 Calendar with 53-Week Years – Hoosier BI
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 ;). |
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. | Proud to be a Super User! |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
86 | |
84 | |
76 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |