Newbie question: Display records by Week order in Columns

[arlequin71]

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"

[Greg_Deckler · Accepted 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"
)

[v-jiascu-msft]

Hi @arlequin71 ,

Could you please mark the proper answers as solutions?

Best Regards,

[tarunsingla · Accepted solution]

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!

[arlequin71]

it works!   Thanks a lot Tarunsingla.

[Greg_Deckler · Accepted 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"
)

[arlequin71]

Thanks Greg.   It works just as i needed.