Find Monthly Min Max Dates

Find when the Min and Max occurred for various months and corresponding dates.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 99
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Find Monthly Min Max Dates with Power Query

Power Query solution 1 for Find Monthly Min Max Dates, proposed by Zoran Milokanović:

let
  Source = Table.AddColumn(
    Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
    "Month", 
    each Text.Start(Date.MonthName([Date], "en-US"), 3)
  ), 
  Get = (t, f) =>
    Text.Combine(
      List.Transform(
        Table.SelectRows(t, each [Amount] = List.Sort(t[Amount], f){0})[Date], 
        each Date.ToText(Date.From(_), "yyyy-MM-dd")
      ), 
      ", "
    ), 
  S = Table.Group(
    Source, 
    {"Month"}, 
    {
      {"Min", each List.Min([Amount])}, 
      {"Min Date", each Get(_, 0)}, 
      {"Max", each List.Max([Amount])}, 
      {"Max Date", each Get(_, - 1)}
    }
  )
in
  S

Power Query solution 2 for Find Monthly Min Max Dates, proposed by Kris Jaganah:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Change = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Amount", Int64.Type}}), 
  Month = Table.AddColumn(Change, "Month", each Date.ToText([Date], "MMM")), 
  Group = Table.Group(
    Month, 
    {"Month"}, 
    {
      {"Min", each List.Min([Amount]), type nullable number}, 
      {"Max", each List.Max([Amount]), type nullable number}, 
      {"All", each _, type table [Date = nullable date, Amount = nullable number, Month = text]}
    }
  ), 
  Expand = Table.ExpandTableColumn(Group, "All", {"Date", "Amount"}, {"All.Date", "All.Amount"}), 
  MinDate = Table.AddColumn(
    Expand, 
    "MinDate", 
    each if [All.Amount] = [Min] then Date.ToText([All.Date], [Format = "yyyy-MM-dd"]) else null
  ), 
  MaxDate = Table.AddColumn(
    MinDate, 
    "MaxDate", 
    each if [All.Amount] = [Max] then Date.ToText([All.Date], [Format = "yyyy-MM-dd"]) else null
  ), 
  Grouped = Table.Group(
    MaxDate, 
    {"Month"}, 
    {
      {"Min", each List.Min([Min]), type nullable number}, 
      {"Min Date", each Text.Combine([MinDate], ", "), type nullable text}, 
      {"Max", each List.Max([Max]), type nullable number}, 
      {"Max Date", each Text.Combine([MaxDate], ", "), type nullable text}
    }
  )
in
  Grouped

Power Query solution 3 for Find Monthly Min Max Dates, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Date = Table.AddColumn(Source, "New Date", each DateTime.ToText([Date], "yyyy-MM-dd")), 
  Month = Table.AddColumn(Date, "Month", each DateTime.ToText([Date], "MMM"), type text), 
  Return = Table.Group(
    Month, 
    {"Month"}, 
    {
      {"Min", each List.Min([Amount])}, 
      {
        "Min Date", 
        each Text.Combine(
          Table.SelectRows(_, (f) => f[Amount] = List.Min([Amount]))[New Date], 
          ", "
        )
      }, 
      {"Max", each List.Max([Amount])}, 
      {
        "Max Date", 
        each Text.Combine(
          Table.SelectRows(_, (f) => f[Amount] = List.Max([Amount]))[New Date], 
          ", "
        )
      }
    }
  )
in
  Return

Power Query solution 4 for Find Monthly Min Max Dates, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Month = Table.AddColumn(Source, "Month", each Text.Start(Date.MonthName(Date.From([Date])), 3)), 
  Sol = Table.Group(
    Month, 
    {"Month"}, 
    {
      {"Min", each List.Min([Amount])}, 
      {
        "Min Date", 
        each 
          let
            a = Table.ToColumns(_), 
            b = List.Min(a{1}), 
            c = List.PositionOf(a{1}, b, Occurrence.All)
          in
            Text.Combine(
              List.Transform(c, each Date.ToText(Date.From(a{0}{_}), [Format = "yyyy-MM-dd"])), 
              ", "
            )
      }, 
      {"Max", each List.Max([Amount])}, 
      {
        "Max Date", 
        each 
          let
            a = Table.ToColumns(_), 
            b = List.Max(a{1}), 
            c = List.PositionOf(a{1}, b, Occurrence.All)
          in
            Text.Combine(
              List.Transform(c, each Date.ToText(Date.From(a{0}{_}), [Format = "yyyy-MM-dd"])), 
              ", "
            )
      }
    }
  )
in
  Sol

Power Query solution 5 for Find Monthly Min Max Dates, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  m = Table.AddColumn(Fonte, "Month", each Date.ToText(Date.From([Date]), "MMM")), 
  gp = Table.Group(
    m, 
    {"Month"}, 
    {
      {
        "Contagem", 
        each [
          Min = List.Min(_[Amount]), 
          #"Min Date" = Text.Combine(
            List.Transform(
              Table.SelectRows(_, each [Amount] = Min)[Date], 
              each Date.ToText(Date.From(_, "en-US"), "yyyy-MM-dd")
            ), 
            ", "
          ), 
          Max = List.Max(_[Amount]), 
          #"Max Date" = Text.Combine(
            List.Transform(
              Table.SelectRows(_, each [Amount] = Max)[Date], 
              each Date.ToText(Date.From(_, "en-US"), "yyyy-MM-dd")
            ), 
            ", "
          )
        ]
      }
    }
  ), 
  res = Table.ExpandRecordColumn(gp, "Contagem", Record.FieldNames(gp[Contagem]{0}))
in
  res

Power Query solution 6 for Find Monthly Min Max Dates, proposed by Matthias Friedmann:

let
  Source = Excel.CurrentWorkbook(){[Name = "MinMaxDate"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Date", type date}, {"Amount", Int64.Type}}
  ), 
  #"Inserted Month Name" = Table.AddColumn(
    #"Changed Type", 
    "Month", 
    each Text.Start(Date.MonthName([Date]), 3), 
    type text
  ), 
  #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Month Name", {{"Date", type text}}), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type1", 
    {"Month"}, 
    {
      {"Min", each List.Min([Amount]), type nullable number}, 
      {
        "Min Date", 
        each 
          let
            min = List.Min([Amount])
          in
            Text.Combine(Table.SelectRows(_, each [Amount] = min)[Date], ", ")
      }, 
      {"Max", each List.Max([Amount]), type nullable number}, 
      {
        "Max Date", 
        each 
          let
            max = List.Max([Amount])
          in
            Text.Combine(Table.SelectRows(_, each [Amount] = max)[Date], ", ")
      }
    }
  )
in
  #"Grouped Rows"

Power Query solution 7 for Find Monthly Min Max Dates, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type1" = Table.TransformColumnTypes(Source, {{"Date", type date}}), 
  S1 = Table.AddColumn(#"Changed Type1", "Month Name", each Date.MonthName([Date]), type text), 
  #"Changed Type" = Table.TransformColumnTypes(S1, {{"Date", type text}}), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Month Name"}, 
    {{"Min", each List.Min([Amount]), type number}, {"Max", each List.Max([Amount]), type number}}
  ), 
  #"Merged Queries" = Table.NestedJoin(
    #"Grouped Rows", 
    {"Month Name", "Min"}, 
    #"Changed Type", 
    {"Month Name", "Amount"}, 
    "Min Date", 
    JoinKind.LeftOuter
  ), 
  Custom1 = Table.TransformColumns(#"Merged Queries", {"Min Date", each Text.Combine(_[Date], ",")}), 
  Custom2 = Table.NestedJoin(
    Custom1, 
    {"Month Name", "Max"}, 
    #"Changed Type", 
    {"Month Name", "Amount"}, 
    "Max Date", 
    JoinKind.LeftOuter
  ), 
  Custom3 = Table.TransformColumns(Custom2, {"Max Date", each Text.Combine(_[Date], ",")}), 
  #"Reordered Columns" = Table.ReorderColumns(
    Custom3, 
    {"Month Name", "Min", "Min Date", "Max", "Max Date"}
  )
in
  #"Reordered Columns"

Solving the challenge of Find Monthly Min Max Dates with Excel

Excel solution 1 for Find Monthly Min Max Dates, proposed by Rick Rothstein:

=TEXTSPLIT(TEXTJOIN("*",,MAP(SEQUENCE(12),LAMBDA(x,LET(tj,LAMBDA(z,TEXTJOIN(", ",,TEXT(z,"yyyy-mm-dd"))),f,FILTER(A2:B366,MONTH(A2:A366)=x),t,TAKE(f,,1),n,DROP(f,,1),s,MIN(n),b,MAX(n),ds,FILTER(t,n=s),db,FILTER(t,n=b),TEXT(28*x,"mmm")&"|"&s&"|"&tj(ds)&"|"&b&"|"&tj(db))))),"|","*")

Excel solution 2 for Find Monthly Min Max Dates, proposed by محمد حلمي:

=LET(n,SEQUENCE(13,,0),z,SEQUENCE(,5),
MAP(n*z^0,z*IF(n,n^0,DROP(n,1)),LAMBDA(r,y,LET(
w,A2:A366,i,MONTH(w)=r,
e,FILTER(B2:B366,i),
p,LAMBDA(q,HSTACK(q,ARRAYTOTEXT(TEXT(
FILTER(FILTER(w,i),q=e),"yyyy-mm-dd")))),
INDEX(IFERROR(HSTACK(TEXT(r*29,"mmm"),
p(MIN(e)),p(MAX(e))),
{"Month","Min","Min Date","Max","Max Date"}),,y)))))

Excel solution 3 for Find Monthly Min Max Dates, proposed by محمد حلمي:

=VSTACK(
{"Month","Min","Min Date","Max","Max Date"},
MAKEARRAY(12,5,LAMBDA(r,c,INDEX(LET(
w,A2:A366,i,MONTH(w)=r,
e,FILTER(B2:B366,i),
p,LAMBDA(q,HSTACK(q,
ARRAYTOTEXT(TEXT(
FILTER(FILTER(w,i),q=e),"yyyy-mm-dd")))),
HSTACK(TEXT(r*29,"mmm"),p(MIN(e)),p(MAX(e)))),c))))

Excel solution 4 for Find Monthly Min Max Dates, proposed by محمد حلمي:

=REDUCE({"Month","Min","Min Date","Max","Max Date"},
SEQUENCE(12),LAMBDA(a,d,LET(
r,A2:A366,
b,B2:B366,
u,MONTH(r)=d,
i,FILTER(b,u),
p,LAMBDA(q,HSTACK(q,ARRAYTOTEXT(TEXT(
FILTER(FILTER(r,u),q=i),"yyyy-mm-dd")))),
VSTACK(a,HSTACK(TEXT(d*29,"mmm"),
p(MIN(i)),p(MAX(i)))))))

Excel solution 5 for Find Monthly Min Max Dates, proposed by Kris Jaganah:

=REDUCE({"Month","Min","Min Date","Max","Max Date"},UNIQUE(MONTH(A2:A366)),LAMBDA(x,y,VSTACK(x,LET(o,A2:A366,p,B2:B366,q,MONTH(o),a,FILTER(p,q=y),b,MIN(a),c,MAX(a),HSTACK(TEXT(y*28,"mmm"),b,ARRAYTOTEXT(FILTER(TEXT(o,"yyyy-mm-dd"),(q=y)*(p=b))),c,ARRAYTOTEXT(FILTER(TEXT(o,"yyyy-mm-dd"),(q=y)*(p=c))))))))

Excel solution 6 for Find Monthly Min Max Dates, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _dt, A2:A366,
 _amt, B2:B366,
 _sdt, EDATE(MIN(_dt), SEQUENCE(12, , 0)),
 _edt, EOMONTH(_sdt, 0),
 _mnth, TEXT(_sdt, "mmm"),
 _min, MINIFS(_amt, _dt, ">=" & _sdt, _dt, "<=" & _edt),
 _max, MAXIFS(_amt, _dt, ">=" & _sdt, _dt, "<=" & _edt),
 _e, LAMBDA(x, y,
 ARRAYTOTEXT(
 TEXT(
 FILTER(_dt, (_amt = y) * (MONTH(_dt) = MONTH(x))),
 "yyyy-mm-dd"
 )
 )
 ),
 _mindt, MAP(_sdt, _min, _e),
 _maxdt, MAP(_sdt, _max, _e),
 _r, HSTACK(_mnth, _min, _mindt, _max, _maxdt),
 _r
)

Excel solution 7 for Find Monthly Min Max Dates, proposed by Oscar Mendez Roca Farell:

=LET(_d,A2:B366,_m,INDEX(_d,,1), HSTACK(UNIQUE(TEXT(_m,"mmm")), TEXTSPLIT(TEXTJOIN("/ ", , MAP(ROW(1:12), LAMBDA(a,LET(_f,FILTER(_d,MONTH(_m)=a),
_i,INDEX(_f,,2), _j,INDEX(_f,,1), fx, LAMBDA(x, LET(_g,AGGREGATE(x,4,_i,1),_g&"|"&ARRAYTOTEXT(TEXT(TOCOL(_j/(_i=_g),2),"yyy-mm-dd")))), fx(15)&"|"&fx(14))))),"|","/")))

Excel solution 8 for Find Monthly Min Max Dates, proposed by Sunny Baggu:

=MAKEARRAY(
 12,
 5,
 LAMBDA(r, c,
 INDEX(
 LET(
 _cond, MONTH(A2:A366) = r,
 _date, TOCOL(IFS(_cond, A2:A366), 3),
 _amt, TOCOL(IFS(_cond, B2:B366), 3),
 _min, MIN(_amt),
 _mindate, ARRAYTOTEXT(TEXT(TOCOL(IFS(_amt = _min, _date), 3), "yyyy-mm-dd")),
 _max, MAX(_amt),
 _maxdate, ARRAYTOTEXT(TEXT(TOCOL(IFS(_amt = _max, _date), 3), "yyyy-mm-dd")),
 HSTACK(TEXT(DATE(2023, r, 1), "mmm"), _min, _mindate, _max, _maxdate)
 ),
 c
 )
 )
)

Excel solution 9 for Find Monthly Min Max Dates, proposed by Sunny Baggu:

=REDUCE(
 {"Month", "Min", "Min Date", "Max", "Max Date"},
 SEQUENCE(12),
 LAMBDA(a, v,
 VSTACK(
 a,
 LET(
 _cond, MONTH(A2:A366) = v,
 _date, TOCOL(IFS(_cond, A2:A366), 3),
 _amt, TOCOL(IFS(_cond, B2:B366), 3),
 _min, MIN(_amt),
 _mindate, ARRAYTOTEXT(TEXT(TOCOL(IFS(_amt = _min, _date), 3), "yyyy-mm-dd")),
 _max, MAX(_amt),
 _maxdate, ARRAYTOTEXT(TEXT(TOCOL(IFS(_amt = _max, _date), 3), "yyyy-mm-dd")),
 HSTACK(TEXT(DATE(2023, v, 1), "mmm"), _min, _mindate, _max, _maxdate)
 )
 )
 )
)

Excel solution 10 for Find Monthly Min Max Dates, proposed by LEONARD OCHEA 🇷🇴:

=LET(d,A2:A366, a,B2:B366, m,UNIQUE(MONTH(d)), w,IF(m=TOROW(MONTH(d)),TOROW(a),""), h,BYROW(w,LAMBDA(x,MAX(x))), l,BYROW(w,LAMBDA(x,MIN(x))), f,LAMBDA(i,BYROW(IF(w=i,TOROW(d),""),LAMBDA(y,TEXTJOIN(", ",1,TEXT(y,"yyyy-mm-dd"))))), VSTACK(HSTACK("Month","Min","Min Date","Max","Max Date"),HSTACK(TEXT("1-"&m,"mmm"),l,f(l),h,f(h))) )

Excel solution 11 for Find Monthly Min Max Dates, proposed by JvdV -:

=DROP(REDUCE(0,ROW(1:12),LAMBDA(x,y,LET(z,FILTER(A2:B366,MONTH(A2:A366)=y),a,TEXT(TAKE(z,,1),"yyyy-mm-dd"),b,DROP(z,,1),VSTACK(x,HSTACK(TEXT(y*30,"mmm"),MIN(b),TEXTJOIN(", ",,REPT(a,b=MIN(b))),MAX(b),TEXTJOIN(", ",,REPT(a,b=MAX(b)))))))),1)

Excel solution 12 for Find Monthly Min Max Dates, proposed by Oscar Javier Rosero Jiménez:

=LET(
_date,A2:A366,
_Val,B2:B366,
_Mes,UNIQUE(TEXT(_date,"mmm")),
_min,MAP(_Mes,LAMBDA(f,MIN(FILTER(_Val,TEXT(_date,"mmm")=f)))),
_minfech, MAP(_Mes,_min,
LAMBDA(_f1,_f2,
TEXTJOIN(", ",,FILTER(TEXT(_date,"yyyy-mm-dd"), (TEXT(_date,"mmm")=_f1) * (_Val=_f2))))),
_Max, MAP(_Mes,LAMBDA(f,MAX(FILTER(_Val,TEXT(_date,"mmm")=f)))),
_MaxDate, MAP(_Mes,_Max,
LAMBDA(_f1,_f2,
TEXTJOIN(", ",,FILTER(TEXT(_date,"yyyy-mm-dd"), (TEXT(_date,"mmm")=_f1) * (_Val=_f2))))),
VSTACK(
HSTACK("Month","min","Min Date","Max","Max Date"),
HSTACK(_Mes,_min,_minfech,_Max,_MaxDate)))

Excel solution 13 for Find Monthly Min Max Dates, proposed by Md Ismail Hosen:

=LAMBDA(Date,
 LET(
 _MonthNameAll, TEXT(TAKE(Date, , 1), "mmm"),
 _OneMonthCalculation, LAMBDA(MonthName,
 LET(
 FilteredData, FILTER(Date, _MonthNameAll = MonthName),
 Dates, CHOOSECOLS(FilteredData, 1),
 Amounts, CHOOSECOLS(FilteredData, 2),
 MinValue, MIN(Amounts),
 MaxValue, MAX(Amounts),
 DateJoiner, LAMBDA(AllDate, TEXTJOIN(", ", TRUE, MAP(AllDate, LAMBDA(Curr, TEXT(Curr, "yyyy-mm-dd"))))),
 MinValueDates, DateJoiner(FILTER(Dates, Amounts = MinValue)),
 MaxValueDates, DateJoiner(FILTER(Dates, Amounts = MaxValue)),
 Result, HSTACK(MonthName, MinValue, MinValueDates, MaxValue, MaxValueDates),
 Result
 )
 ),
 _MonthName, UNIQUE(_MonthNameAll),
 _Result, DROP(REDUCE("", _MonthName, LAMBDA(Acc, Curr, VSTACK(Acc, _OneMonthCalculation(Curr)))), 1),
 _Result
 )
)(Sheet1!A2:B366)

&&&

Solving the challenge of Find Monthly Min Max Dates with Power Query

Solving the challenge of Find Monthly Min Max Dates with Excel

Leave a Reply Cancel reply