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.
Hello, could anyone help me on this?. I have the following table
and i need to address records according to week number of the month (current filtered month). So the report table should look like this:
Each record should be address to 1st, 2d, 3rd, 4th weekly forecast estimate period according to its specific date.
Ideally the number of weekly periods by month should be configurable (4 or five depending on the specific month).
I really appreciate your help in advance,
regards,
Alex-
This is the source table:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCspMzkgsSlHSUXJ0dwOS5mZm5uZAOtgFxDM0MjAwANEGUFohLTVJwcjA0FIpVge3ZlfXICBpBNNkCaGNUDU7F2UWl2TmJQJlvLw9QbrNLYyMgbS3lwdIlwlUtzE1dFuQp9vIFGK3EUX+JtPpMMvR3O6Vn5EHFHYPAdlsZGxsZAGkfby9gKQx1GaQTizRRYJOc7J1GpGVRowMDTA9GwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Customer = _t, CutomerCode = _t, Category = _t, Fcst = _t, WeeklyEstimate = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Customer", type text}, {"CutomerCode", Int64.Type}, {"Category", type text}, {"Fcst", Int64.Type}, {"WeeklyEstimate", Int64.Type}, {"Date", type date}}) in #"Changed Type"
Solved! Go to Solution.
Perhaps add a column:
WeekNum = WEEKNUM([Date])
And then another column:
WeekOfMonth = VAR __currentMonth = MONTH([Date]) VAR __currentYear = YEAR([Date]) VAR __first = MINX(FILTER(ALL('Table12'),MONTH([Date]) = __currentMonth && YEAR([Date]) = __currentYear),[WeekNum]) VAR __last = MAXX(FILTER(ALL('Table12'),MONTH([Date]) = __currentMonth && YEAR([Date]) = __currentYear),[WeekNum]) RETURN SWITCH(TRUE(), [WeekNum] = __first,"First", [WeekNum] = __last,"Last", [WeekNum] = __first + 1,"Second", [WeekNum] = __first + 2,"Third", [WeekNum] = __first + 3,"Fourth" )
You need two steps:
1. Add new column to get Week number of Month.
2. Pivot the weekly forecase on the week num.
Here's the code from the advanced editor, for the desired output:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCspMzkgsSlHSUXJ0dwOS5mZm5uZAOtgFxDM0MjAwANEGUFohLTVJwcjA0FIpVge3ZlfXICBpBNNkCaGNUDU7F2UWl2TmJQJlvLw9QbrNLYyMgbS3lwdIlwlUtzE1dFuQp9vIFGK3EUX+JtPpMMvR3O6Vn5EHFHYPAdlsZGxsZAGkfby9gKQx1GaQTizRRYJOc7J1GpGVRowMDTA9GwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Customer = _t, CutomerCode = _t, Category = _t, Fcst = _t, WeeklyEstimate = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Customer", type text}, {"CutomerCode", Int64.Type}, {"Category", type text}, {"Fcst", Int64.Type}, {"WeeklyEstimate", Int64.Type}, {"Date", type date}}),
#"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Week of Month",{"Date"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Week of Month", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Week of Month", type text}}, "en-US")[#"Week of Month"]), "Week of Month", "WeeklyEstimate", List.Sum)
in
#"Pivoted Column"
You can rename the columns (1,2,3,etc.) accordingly.
Regards,
Tarun
Did I answer your question? Mark my post as a solution!
Hi @arlequin71 ,
Could you please mark the proper answers as solutions?
Best Regards,
You need two steps:
1. Add new column to get Week number of Month.
2. Pivot the weekly forecase on the week num.
Here's the code from the advanced editor, for the desired output:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCspMzkgsSlHSUXJ0dwOS5mZm5uZAOtgFxDM0MjAwANEGUFohLTVJwcjA0FIpVge3ZlfXICBpBNNkCaGNUDU7F2UWl2TmJQJlvLw9QbrNLYyMgbS3lwdIlwlUtzE1dFuQp9vIFGK3EUX+JtPpMMvR3O6Vn5EHFHYPAdlsZGxsZAGkfby9gKQx1GaQTizRRYJOc7J1GpGVRowMDTA9GwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Customer = _t, CutomerCode = _t, Category = _t, Fcst = _t, WeeklyEstimate = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Customer", type text}, {"CutomerCode", Int64.Type}, {"Category", type text}, {"Fcst", Int64.Type}, {"WeeklyEstimate", Int64.Type}, {"Date", type date}}),
#"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Week of Month",{"Date"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Week of Month", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Week of Month", type text}}, "en-US")[#"Week of Month"]), "Week of Month", "WeeklyEstimate", List.Sum)
in
#"Pivoted Column"
You can rename the columns (1,2,3,etc.) accordingly.
Regards,
Tarun
Did I answer your question? Mark my post as a solution!
it works! Thanks a lot Tarunsingla.
Perhaps add a column:
WeekNum = WEEKNUM([Date])
And then another column:
WeekOfMonth = VAR __currentMonth = MONTH([Date]) VAR __currentYear = YEAR([Date]) VAR __first = MINX(FILTER(ALL('Table12'),MONTH([Date]) = __currentMonth && YEAR([Date]) = __currentYear),[WeekNum]) VAR __last = MAXX(FILTER(ALL('Table12'),MONTH([Date]) = __currentMonth && YEAR([Date]) = __currentYear),[WeekNum]) RETURN SWITCH(TRUE(), [WeekNum] = __first,"First", [WeekNum] = __last,"Last", [WeekNum] = __first + 1,"Second", [WeekNum] = __first + 2,"Third", [WeekNum] = __first + 3,"Fourth" )
Thanks Greg. It works just as i needed.
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 |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |