Forum Discussion

lgo's avatar
lgo
Frequent Visitor
2 years ago
Solved

How to filter by last value provided within a month

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!

Need Help
  • slorin's avatar
    slorin
    2 years ago

    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 

5 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    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's avatar
      lgo
      Frequent Visitor
      2 years ago

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

  • collinsg's avatar
    collinsg
    Solution Sage
    2 years ago

    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's avatar
      lgo
      Frequent Visitor
      2 years ago

      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 😕

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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"