Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, need help to add this column in my Calendar table where I have date and week_number as columns. So my new column, I need to put the week number and the first and last day of the week for example:
29 07/16 to 07/22
Thanks in advance for any help.
Solved! Go to Solution.
Hi
I'm not sure whether I understood your problem correctly, however M query to reach to the answer is as follows.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NzpVgdINcIlWuMyjVB5Zqics0Q3FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Inserted Week of Year" = Table.AddColumn(#"Changed Type", "Week of Year", each Date.WeekOfYear([Date]), type number), #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Year", "Start of Week", each Date.StartOfWeek([Date]), type date), #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date]), type date), #"Inserted Month" = Table.AddColumn(#"Inserted End of Week", "Month", each Date.Month([Date]), type number), #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Date]), type number), #"Inserted Day1" = Table.AddColumn(#"Inserted Day", "Day.1", each Date.Day([Start of Week]), type number), #"Renamed Columns" = Table.RenameColumns(#"Inserted Day1",{{"Day.1", "Week Start Day"}}), #"Inserted Day2" = Table.AddColumn(#"Renamed Columns", "Day.1", each Date.Day([End of Week]), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Day2",{{"Day.1", "Week End Day"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Week of Year", type text}, {"Week Start Day", type text}, {"Week End Day", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "WeekNo_Start&End Date", each [Week of Year]&" "&[Week Start Day]&"/"&[Week End Day]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"WeekNo_Start&End Date", type text}}) in #"Changed Type2"
Hello,
A DAX expression could:
WEEK = [WEEKNO] & " " & CALCULATE ( MIN ( [Date] ), FILTER ( ALL ( 'KALENDER' ), [WEEKNO] = EARLIER ( [WEEKNO] ) && [YEARNO] = EARLIER ( [YEARNO] ) ) ) & " to " & CALCULATE ( MAX ( [Date] ), FILTER ( ALL ( 'KALENDER' ), [WEEKNO] = EARLIER ( [WEEKNO] ) && [YEARNO] = EARLIER ( [YEARNO] ) ) )
Thanks
Hi
I'm not sure whether I understood your problem correctly, however M query to reach to the answer is as follows.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NzpVgdINcIlWuMyjVB5Zqics0Q3FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Inserted Week of Year" = Table.AddColumn(#"Changed Type", "Week of Year", each Date.WeekOfYear([Date]), type number), #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Year", "Start of Week", each Date.StartOfWeek([Date]), type date), #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date]), type date), #"Inserted Month" = Table.AddColumn(#"Inserted End of Week", "Month", each Date.Month([Date]), type number), #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Date]), type number), #"Inserted Day1" = Table.AddColumn(#"Inserted Day", "Day.1", each Date.Day([Start of Week]), type number), #"Renamed Columns" = Table.RenameColumns(#"Inserted Day1",{{"Day.1", "Week Start Day"}}), #"Inserted Day2" = Table.AddColumn(#"Renamed Columns", "Day.1", each Date.Day([End of Week]), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Day2",{{"Day.1", "Week End Day"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Week of Year", type text}, {"Week Start Day", type text}, {"Week End Day", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "WeekNo_Start&End Date", each [Week of Year]&" "&[Week Start Day]&"/"&[Week End Day]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"WeekNo_Start&End Date", type text}}) in #"Changed Type2"
Hello,
A DAX expression could:
WEEK = [WEEKNO] & " " & CALCULATE ( MIN ( [Date] ), FILTER ( ALL ( 'KALENDER' ), [WEEKNO] = EARLIER ( [WEEKNO] ) && [YEARNO] = EARLIER ( [YEARNO] ) ) ) & " to " & CALCULATE ( MAX ( [Date] ), FILTER ( ALL ( 'KALENDER' ), [WEEKNO] = EARLIER ( [WEEKNO] ) && [YEARNO] = EARLIER ( [YEARNO] ) ) )
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |