Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
103 | |
87 | |
77 | |
70 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |