Sales Based on Custom Month Range

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Custom1 = Table.Group(Table.TransformColumns(Source, {"Date", each [ a=Date.EndOfMonth(_),b=Date.DayOfWeek(Date.EndOfMonth(_),Day.Friday),c=Date.StartOfMonth(_),d=6-Date.DayOfWeek(Date.StartOfMonth(_),Day.Friday),e=if _hashtag#date(2023,12,10)) then _ else if _>Date.AddDays(c,-d) then _ else Date.AddDays(_,-10) else Date.AddDays(_,10), f=Date.MonthName(e) ][f]}),"Date",{"x",each List.Sum([Sales])})
in
 Custom1
                    
                  
          

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Last = List.Transform({2..13}, each let d = hashtag#date(2023,_,1) in try d-Duration.From(Number.Mod(Date.DayOfWeek(d,5)+6,7)+1) otherwise hashtag#date(2023,12,31) ),
 Ans = Table.Group(Table.AddColumn(Source,"Month",each Date.ToText(List.First(List.Select(Last,(l)=> l>= Date.From([Date]))),"MMM")), "Month",{"Total Sale" ,each List.Sum([Sales])})
in
 Ans


                    
                  
          

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  MonthShort = Table.AddColumn(
    Source, 
    "Month", 
    each 
      let
        d  = Date.From([Date]), 
        lf = Date.AddDays(Date.EndOfMonth(d), - Date.DayOfWeek(Date.EndOfMonth(d), Day.Friday))
      in
        Text.Start(
          Date.MonthName(if d <= lf or Date.Month(d) = 12 then d else Date.AddDays(d, 7)), 
          3
        )
  ), 
  S = Table.Group(MonthShort, {"Month"}, {{"Total Sales", each List.Sum([Sales])}})
in
  S

let
 Source = List.Transform({Number.From(hashtag#date(2023,01,01))..Number.From(hashtag#date(2023,12,31))}, Date.From),
 Tabla = Table.FromColumns({Source, List.Transform(Source, each Text.Start(Date.MonthName(_), 3)), List.Transform(Source, Date.DayOfWeekName)}, {"Date", "Mes", "Days"}),
 Ajuste = Table.FillDown(Table.FillUp(Table.AddColumn(Tabla, "Month", each if [Days] = "Friday" then [Mes] else null), {"Month"}), {"Month"})[[Date], [Month]],
 Sol = Table.Group(Table.AddColumn(Ajuste, "Total Sales", each try Table1{[Date = DateTime.From([Date])]}[Sales] otherwise null), {"Month"}, {{"Total Sales", each List.Sum([Total Sales])}})
in
 Sol


                    
                  
          

let
 Fonte = Tabela1,
 p = let
 a = Table.FromList(List.Transform({Number.From(hashtag#date(2023,01,01))..Number.From(hashtag#date(2023,12,31))}, Date.From),Splitter.SplitByNothing(), null, null),
 b = Table.AddColumn(a, "Personalizar", each 
 if Date.DayOfWeekName([Column1]) = "Friday" then Date.DayOfWeekName([Column1])& Date.ToText([Column1],"MMM") else null )
 in
 Table.FillDown(Table.FillUp(b,{"Personalizar"}),{"Personalizar"}),
 tipo = Table.TransformColumnTypes(Fonte,{{"Date", type date,"en-us"}}),
 mesc = Table.NestedJoin(p, {"Column1"}, tipo, {"Date"}, "Fonte", JoinKind.LeftOuter),
 ex = Table.ExpandTableColumn(mesc, "Fonte", {"Sales"}, {"Sales"}),
 add = Table.AddColumn(ex, "Month", each Text.End([Personalizar],3) ),
 res = Table.Group(add, {"Month"}, {{"Total Sales", each List.Sum([Sales]), type nullable number}})
in
 res


                    
                  
          

let
 Source = Excel.CurrentWorkbook(){[Name="tData92"]}[Content],
 ChangeType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
 Add_Month = Table.AddColumn(ChangeType, "Month", each let
 _EOM = Date.EndOfMonth([Date]),
 _LastFriday = Date.AddDays(_EOM, -Date.DayOfWeek(_EOM,Day.Friday)*Number.From(Date.Month(_EOM)<>12)),
 _Month = hashtag#date(1900,Date.Month([Date]) + Number.From([Date]>_LastFriday),1)
 in Text.Start(Date.MonthName(_Month,"en-US"),3) ),
 Group = Table.Group(Add_Month, "Month", {"Total Sales", each List.Sum([Sales])})
in
 Group
                    
                  
          

let
  Source = Data, 
  Month = Table.AddColumn(
    Source, 
    "Month", 
    each [
      a = Date.StartOfWeek(Date.EndOfMonth([Date]), Day.Friday), 
      b = Date.StartOfWeek(Date.EndOfMonth(Date.AddMonths([Date], 1)), Day.Friday), 
      c = if Date.Month([Date]) = 12 then Date.EndOfMonth([Date]) else if [Date] <= a then a else b, 
      d = Text.Start(Date.MonthName(c), 3)
    ][d]
  ), 
  Output = Table.Group(Month, {"Month"}, {{"Sales", each List.Sum([Sales]), type nullable number}})
in
  Output

=LET(d,A2:A114,m,SEQUENCE(12),y,2023,e,(m+1)&"/"&y,t,IFERROR(e-WEEKDAY(e,16),DATE(y,13,)),
HSTACK(TEXT(t-1,"mmm"),SUMIFS(B2:B114,d,">="&VSTACK("1/"&y,DROP(t,-1)+1),d,"<="&t)))

=LET(a,A2:A114,e,UNIQUE(WORKDAY.INTL(EOMONTH(+a,0)+1,-1,"1111011")),r,IF(MONTH(e)=12,EOMONTH(e,0),e),
HSTACK(TEXT(r,"mmm"),SUMIFS(B2:B114,a,">"&VSTACK(0,DROP(r,-1)),a,"<="&r)))

=LET(a,A2:A114,e,UNIQUE(WORKDAY.INTL(EOMONTH(+a,0)+1,-1,"1111011")),r,IF(MONTH(e)=12,EOMONTH(e,0),e),HSTACK(TEXT(r,"mmm"),MAP(r,VSTACK(0,DROP(r,-1)),LAMBDA(v,d,SUM(IF((a>d)*(a<=v),B2:B114))))))

=LET(a,A2:A114,b,B2:B114,c,YEAR(TAKE(a,1)),d,DATE(c,12,31),e,EOMONTH(d,-12),f,DATE(c,1,SEQUENCE((d-e))),h,MAP(SEQUENCE(12),LAMBDA(x,MAX(FILTER(f,(MONTH(f)=x)*(WEEKDAY(f,2)=5))))),i,IF(MONTH(h)=12,MAX(f),h),j,VSTACK(e,DROP(i,-1))+1,VSTACK({"Month","Total Sales"},HSTACK(TEXT(h,"Mmm"),MAP(j,i,LAMBDA(v,w,SUM(((a)>=v)*((a)<=w)*b))))))

=LET(
 _seq, DATE(2023, SEQUENCE(12), 1),
 _end, VSTACK(
 DROP(MAP(_seq, LAMBDA(a, EOMONTH(a, 0) + 1 - WEEKDAY(EOMONTH(a, 0) + 1 - 6))), -1),
 DATE(2023, 12, 31)
 ),
 _start, VSTACK(DATE(2023, 1, 1), DROP(_end + 1, -1)),
 _sales, DROP(
 REDUCE(
 "",
 SEQUENCE(12),
 LAMBDA(a, v,
 VSTACK(
 a,
 SUM(FILTER(B2:B114, (A2:A114 >= INDEX(_start, v, 1)) * (A2:A114 <= INDEX(_end, v, 1))))
 )
 )
 ),
 1
 ),
 HSTACK(TEXT(_seq, "mmm"), _sales)
)

=LET(t,A2:B114,d,TAKE(t,,1),s,TAKE(t,,-1),m,MAP(d,LAMBDA(a,LET(f,EOMONTH(a,0),IF(a<=f+1-WEEKDAY(f,15),MONTH(a),MONTH(a)+IF(MONTH(a)=12,0,1))))),u,UNIQUE(m),VSTACK(HSTACK("Month","Total Sales"),HSTACK(TEXT(u&"-23","mmm"),MMULT((TOROW(m)=u)*TOROW(s),m^0))))

=LET(a,A3:B115,
b,TAKE(a,,1),
c,TAKE(a,,-1),
d,MAP(b,LAMBDA(z,EOMONTH(z,0)-CHOOSE(WEEKDAY(EOMONTH(z,0),2),3,4,5,6,0,1,2))),
e,MAP(b,d,LAMBDA(x,y,IF(x<=y,y,IF(INDIRECT(ADDRESS(ROW(x)+1,1))=0,x,
INDIRECT(ADDRESS(ROW(x)+1,1)))))),
f,TEXT(e,"mmm"),
g,UNIQUE(f),
_Val,MAP(g,LAMBDA(q,SUM((f=q)*(c)))), 
HSTACK(g,_Val))

from datetime import datetime, timedelta
df = xl("A1:B114", headers=True)
df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)
def last_friday(year: int, month: int):
 last_day = datetime(year, month + 1, 1) - timedelta(days=1)
 offset = (last_day.weekday() - 4) % 7
 return last_day - timedelta(days=offset)
def calculate_monthly_sales(df):
 sales, date = {}, datetime(2023, 1, 1)
 while date.year < 2024:
 y, m = date.year, date.month
 start = datetime(y, 1, 1) if m == 1 else last_friday(y, m - 1) + timedelta(1)
 end = datetime(y, 12, 31) if m == 12 else last_friday(y, m)
 sales[f'{y}-{m:02d}'] = df.loc[(df['Date'] >= start) & (df['Date'] <= end), 'Sales'].sum()
 date = (date + timedelta(32)).replace(day=1)
 return sales
monthly_sales_df = pd.DataFrame(calculate_monthly_sales(df).items(), columns=['Month', 'Total Sales'])
monthly_sales_df['Month'] = pd.to_datetime(monthly_sales_df['Month'], format='%Y-%m').dt.strftime('%b')
monthly_sales_df