how to write formula when doing query

Hi Anonymous 

Profs? 🤔 I think I like that 😊

Paste this in a blank query tosee the steps and adapt it to your table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lci5DQAgCADAXagt+HUXwv5rSIDqkosAYYE8pWCr6CPNK+/zbQ11JFrfyNY60lqfHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Index", type text}}, "en-US"), "Index", Splitter.SplitTextByPositions({0, 2}, true), {"Month", "Day"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Month", Int64.Type}, {"Day", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Month] > (Date.Month(DateTime.LocalNow()) - 3) and [Month] <= Date.Month(DateTime.LocalNow()))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Latest in month", each List.Max(Table.SelectRows(#"Filtered Rows", (inner)=> inner[Month] = [Month])[Day])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Latest in month] = [Day]))
in
    #"Filtered Rows1"

It could be more compact but I've separated each of the steps  on purpose for the sake of clarity.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

AlB  you've missed Feb/Jan (2, 1) and the case they have data from last year:
If it's february the code will get only Jan & Feb's binaries, and if it's June but they have binaries from last December the code will get those as well.

AnonymousThis should work as long as you don't have more than a year's data in the same folder. If you do, you need to add a Year dimension in your index.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WystX0lEyNjJWitWBcYwNEBwTAzMkjiGSMhMjiLLK1GIwzxwhZWpggsQxNETmWCBpMjUyReKZGVggFBoaIbvC0AjkwFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Index = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    Index2 = Table.AddColumn(PreviousStep, "Index2", each ([Index] - Number.Mod([Index], 100))/100, type number),
    Last3m = let d = DateTime.LocalNow(), l = {Date.Month(d), Date.Month(Date.AddMonths(d, -1)), Date.Month(Date.AddMonths(d, -2))} in Table.SelectRows(Index2, each [Index2] = l{0} or [Index2] = l{1} or [Index2] = l{2}),
    MaxDate = Table.SelectColumns(Table.Group(Last3m, {"Index2"}, {{"Max", each List.Max([Index]), type number}}),{"Max"}),
    RemoveOther = Table.RemoveColumns(Table.NestedJoin(PreviousStep, {"Index"}, MaxDate, {"Max"}, "d", JoinKind.Inner), {"d"}),
    AddDate = let d = DateTime.LocalNow() in Table.AddColumn(RemoveOther, "Month", each if [Index]-(Date.Month(d)+1)*100 > 0 then #date(Date.Year(d)-1, ([Index] - Number.Mod([Index], 100))/100, 1) else #date(Date.Year(d), ([Index] - Number.Mod([Index], 100))/100, 1), type date)
in
    AddDate

Or change #"Filtered Rows" in AIB's code to Last3m.