Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I have a week number (1,2,...,52) and I have the year, I'm looking for a function that could transform\convert the week and the year into date.
for some reason I didn't manage to do that.
Tank you..:)
Solved! Go to Solution.
As I'm not aware of a function that returns the date of the week, instead I'd create a calendar for the year (replace "YourYear" respectively), create the week numbers and their first dates. Then filter on your week-numbers:
let Source = {Number.From(#date(YourYear,01,01))..Number.From(#date(YourYear,12,31))}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), WeekColumn = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Column1])), StartOfWeek = Table.AddColumn(WeekColumn, "StartOfWeek", each Date.StartOfWeek([Column1])), #"Filtered Rows1" = Table.SelectRows(StartOfWeek, each [Week] >= List.Min(YourWeekList) and [Week] <= List.Min(YourWeekList)) in #"Filtered Rows1"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Another way is to insert a new new column (Power BI desktop >> New Measure >> New Column)
ndate = DATE([year],1,-2)-WEEKDAY(DATE([year],1,3))+[week]*7
This is based on the ISO week date, which means we need to find the Monday nearest to the 1st of January.
Thanks, this is great and brief.
Hello @wgarn ! This would actually solve the issue I have had but the code you had there returns Expression.Error DATE was not recocnized. Do you know the reason? Same actually comes with WEEKDAY.
@wgarn ISO Week Number 1 is the week (Mo-Su) that contains the 4th of January.
For the correct rules check (the comments below) my video.
Edit: ah, you mean Monday closest to January 1st is the start of week 1?
That looks like another correct way of formulating ISO week 1.
Do you expect to create one date per week (if yes: which one? First, last?) or all days?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Yes, one data per week- the first day.
Thanks!
As I'm not aware of a function that returns the date of the week, instead I'd create a calendar for the year (replace "YourYear" respectively), create the week numbers and their first dates. Then filter on your week-numbers:
let Source = {Number.From(#date(YourYear,01,01))..Number.From(#date(YourYear,12,31))}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), WeekColumn = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Column1])), StartOfWeek = Table.AddColumn(WeekColumn, "StartOfWeek", each Date.StartOfWeek([Column1])), #"Filtered Rows1" = Table.SelectRows(StartOfWeek, each [Week] >= List.Min(YourWeekList) and [Week] <= List.Min(YourWeekList)) in #"Filtered Rows1"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Apologies for resurrecting an old thread, but i've implemented this solution and it's hit a snag - The code generates a column of tables as it should but when I then try and insert a step after it to expand out the "startofweek" column, I get an error:
"gaweek" is the name of the "week" column in my source data which is drawn from Google Analytics (which I replaced the "yourweeklist" tags with in the supplied code as I think I was supposed to.)
great, it was vary helpful.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
103 | |
87 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |