How can I keep the row of the max date associated with each month in table via M code?

[naoyixue1]

Hey there,

Is there any way for me to only keep the rows with the last 'Week Ending' dateper month in this report. The purpose is that I want to keep the lookup table to only reflect the month ending date. In below case, I want to only highlight the Month, YR and the last WeekEnding date in each month. Do you have any thoughts? Thanks! (I only want to do it use M code, since I want to use this table as an assist table to attach to another fact table via SQL)

[gmsamborn · Accepted solution]

Hi @naoyixue1 

Would something like this help?

let
    StartDate = #date(Date.Year(DateTime.LocalNow()), 1, 1),
    NumberOfDays = if Date.IsLeapYear(Date.Year(DateTime.LocalNow())) then 366 else 365,
    ListOfDates = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
    FilteredDates = List.Select(ListOfDates, each Date.DayOfWeek(_, Day.Sunday) = 5),
    ConvertedToTable = Table.FromList(FilteredDates, Splitter.SplitByNothing(), {"Date"} ),
    AddedMonthYear = Table.AddColumn(ConvertedToTable, "MonthYear", each Date.StartOfMonth([Date])),
    GroupedRows = Table.Group(AddedMonthYear, {"MonthYear"}, {{"LastFriday", each List.Max([Date]), type date}})
in
    GroupedRows

Let me know if that is what you wanted or not.  (I thought about adding that column to a date table.)

[Ahmedx]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMjIwtABShvpm+mBmrA6GjKExTikjA9xS5ggpt9QkhJSRvjFOGUMD3FK4zTMyQUj5JhYhpIyRrUKTQbYKXcocpxQ+qwyhUrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, YR = _t, #"Week End" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"YR", Int64.Type}, {"Week End", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Week End", type date}}, "en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Month", "YR"}, {{"Count", (x)=> Table.FromRecords({ Table.Max(x,"Week End")})}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Month", "YR", "Week End"}, {"Month", "YR", "Week End"})
in
    #"Expanded Count"

[naoyixue1]

@danextian Thanks....I know that's the dax to add new column, but I want to add it from the original table. Otherwise, SQL will not take the column I added...That's why I just only wwant to know the M code. Thank you though. 

[danextian · Accepted solution]

Hi @naoyixue1 ,

Try this custom column

let 
x = [Month],
y = [YR],
z = #"Changed Type"
in
Table.Group(Table.SelectRows(z, each [Month] = x and [YR] = y),{"Month", "YR"}, {{"Max", each List.Max([Week Ending]), type nullable date}})[Max]{0}

Replace x and y with the actual names in your table and z with the name of the previously applied step.

Here's the complete sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMjIwtABSBmb6Bob6YE6sDpqcoTFuOSMDPHLmKHJuqUlI9gHNNMIhZ2iAR84ct5yRCYqcb2IRqn3GOORA9uGUM8ctB7IPl5yxIYqcYwGyW4BmmuCQMzRByMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, YR = _t, #"Week Ending" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"YR", Int64.Type}, {"Week Ending", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Max Week Ending", each let 
x = [Month],
y = [YR],
z = #"Changed Type"
in
Table.Group(Table.SelectRows(z, each [Month] = x and [YR] = y),{"Month", "YR"}, {{"Max", each List.Max([Week Ending]), type nullable date}})[Max]{0}, type date)
in
    #"Added Custom"

[gmsamborn · Accepted solution]

Hi @naoyixue1 

Would something like this help?

let
    StartDate = #date(Date.Year(DateTime.LocalNow()), 1, 1),
    NumberOfDays = if Date.IsLeapYear(Date.Year(DateTime.LocalNow())) then 366 else 365,
    ListOfDates = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
    FilteredDates = List.Select(ListOfDates, each Date.DayOfWeek(_, Day.Sunday) = 5),
    ConvertedToTable = Table.FromList(FilteredDates, Splitter.SplitByNothing(), {"Date"} ),
    AddedMonthYear = Table.AddColumn(ConvertedToTable, "MonthYear", each Date.StartOfMonth([Date])),
    GroupedRows = Table.Group(AddedMonthYear, {"MonthYear"}, {{"LastFriday", each List.Max([Date]), type date}})
in
    GroupedRows

Let me know if that is what you wanted or not.  (I thought about adding that column to a date table.)

[danextian]

Hi @naoyixue1 ,

Try this calculated column:

=
CALCULATE (
    MAX ( data[week ending] ),
    ALLEXCEPT ( data, data[month], data[yr] )
) = data[week ending]

This will calculate for the max ending date based on month and yr columns and compare it against the current row week ending date.  Anything true is the max.