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...
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 

collinsg's avatar
collinsg
Icon for Solution Sage rankSolution 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 😕