How to filter by last value provided within a month

[lgo]

Hello! 

My data is provided weekly but i would like to get it monthly. The rule should be: latest value provided within a month = monthly value. Is that possible?

My data looks like this, see example below:

07/01/2023

14/01/2023

21/01/2023

28/01/2023

04/02/2023

11/02/2023

18/02/2023

25/02/2023

....

so in that case, i would only filter 28/01/2023 & 25/02/2023. I have tried this but it is not working, as it take the the date just when it really coincides with the end of a month! (i.e. 31/01/2023)

= Table.SelectRows(#"Changed Type1", each if Date.IsInCurrentMonth([Date]) then [Date] = List.Max(#"Changed Type1"[Date]) else [Date] = Date.EndOfMonth([Date]))

could somebody give me a hand? thanks a lot in advance!

[dufoq3]

There are many ways:

Edit 2nd step YourSource = Source (refer to your data)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDUNzIwMlaK1YlWMjRB4RoZonItULgGQMVGSHoNUbkWKFwjUwQ3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    YourSource = Source,
    Ad_YearMonth = Table.AddColumn(YourSource, "Year Month", each Number.From(Date.ToText(Date.From([Date], "sk-SK"), "yyyyMM")), Int64.Type),
    #"Grouped Rows" = Table.Group(Ad_YearMonth, {"Year Month"}, {{"Date", each List.Max([Date]), type nullable date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Date"})
in
    #"Removed Other Columns"

[slorin · Accepted solution]

Hi,

Maybe with Table.Group

= Table.FromRecords(
    Table.Group(
       Prev_Step, 
       {"Date"},
       {{"Data", each Table.Max(_,"Date"), type record}},
       GroupKind.Local,
       (x,y) => Byte.From(Date.StartOfMonth(x[Date])<>Date.StartOfMonth(y[Date]))
     )[Data]
   )

Stéphane 

[lgo]

It looks like this is working, thanks a lot Stephane!!!

[collinsg]

Good day Igo,

Here is a suggestion. Each date is a Saturday so,

1. Calculate the date of the last Saturday in the month.
   1. First find the end of the month.
   2. Find the start of the week the end of month falls in, where the start of the week is a Saturday.
2. Select rows with date = date of the last Saturday.

= Table.SelectRows(PreviousStep, each [Date] = Date.StartOfWeek( Date.EndOfMonth([Date]), Day.Saturday ) )

Hope this helps.

[lgo]

Thank you so much for your answer collinsg! However... not sure I could use this option because when the operator is not available on sundays, it can fill in the number on fridays, or sundays, or even on mondays! Hence that solution cannot be used. Thanks anyway for your help! I am still trying to find out an answer 😕