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 

slorin's avatar
slorin
Icon for Super User rankSuper 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!!!