Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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! |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
83 | |
72 | |
49 |
User | Count |
---|---|
143 | |
130 | |
108 | |
64 | |
55 |