Monthly Sales Split Calendar Days

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Record = Table.AddColumn(
    Source, 
    "R", 
    each [
      S = Date.From([From Date]), 
      E = Date.From([To Date]), 
      TD = Number.From(E - S) + 1, 
      G = List.Generate(
        () => [D = 1, C = Date.AddDays(S, - 1), RT = 0, Y = 0], 
        (x) => x[D] > 0, 
        (x) => [
          O  = Date.AddDays(x[C], 1), 
          C  = List.Min({Date.EndOfMonth(O), E}), 
          D  = Number.From(C - O) + 1, 
          Y  = Date.Year(O), 
          MS = Number.Round([Sales] * D / TD, 2), 
          RT = MS + (if x[Y] = Y then x[RT] else 0)
        ], 
        (x) => [
          Emp           = [Emp], 
          From Date     = x[O], 
          To Date       = x[C], 
          Monthly Sales = x[MS], 
          Running Total = x[RT]
        ]
      ), 
      R = List.Skip(G)
    ][R]
  ), 
  Return = Table.FromRecords(List.Combine(Record[R]))
in
  Return

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sales = Table.AddColumn(
    Source, 
    "Sales per Day", 
    each [Sales] / (Number.From([To Date] - [From Date]) + 1)
  ), 
  Days = Table.ExpandListColumn(
    Table.AddColumn(
      Sales, 
      "Days", 
      each List.Transform({Number.From([From Date]) .. Number.From([To Date])}, Date.From)
    ), 
    "Days"
  ), 
  EoM = Table.AddColumn(Days, "EoM", each Date.EndOfMonth([Days])), 
  Years = Table.AddColumn(EoM, "Year", each Date.Year([Days])), 
  Group = Table.Group(
    Years, 
    {"Emp", "Year", "EoM"}, 
    {
      {
        "A", 
        each Table.FromRows(
          {{[Days]{0}, List.Last([Days]), List.Sum([Sales per Day])}}, 
          {"From Date", "To Date", "Monthly Sales"}
        )
      }
    }
  ), 
  ExpandB = Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0})), 
  GroupB = Table.Group(
    ExpandB, 
    {"Emp", "Year"}, 
    {
      {
        "A", 
        each 
          let
            a = [Monthly Sales], 
            b = List.Transform({1 .. List.Count(a)}, each List.Sum(List.FirstN(a, _))), 
            c = Table.FromColumns(
              List.Skip(Table.ToColumns(_), 3) & {b}, 
              List.Skip(Table.ColumnNames(_), 3) & {"RT"}
            )
          in
            c
      }
    }
  ), 
  Sol = Table.RemoveColumns(
    Table.ExpandTableColumn(GroupB, "A", Table.ColumnNames(GroupB[A]{0})), 
    "Year"
  )
in
  Sol

let
 Fonte = Tabela1,
 add = Table.AddColumn(Fonte, "Personalizar", each 
let
n = (Number.From([To Date]-[From Date])+1),
a = [Sales] / n,
b = List.Dates(Date.From([From Date]),n,hashtag#duration(1,0,0,0)),
c = List.Zip({List.Transform(b,each Text.From(Date.Year(_))&"/"&Text.From(Date.Month(_))),b,List.Repeat({a},List.Count(b))}),
d = List.Distinct(List.Transform(c, (x)=> List.Select(c, each _{0} = x{0}))),
e = List.Transform(d, each List.Sum(List.Transform(_, (x)=> x{2}))),
f = Table.FromRows(List.Zip({e,List.RemoveFirstN(List.Accumulate(e,{0}, (s,c)=> s&{List.Last(s)+c}))}),{"Monthly Sales","Running Total"})
in
f ),
 res = Table.ExpandTableColumn(add, "Personalizar", Table.ColumnNames(add[Personalizar]{0}) )
in
 res


                    
                  
          

let
  Source = Excel.CurrentWorkbook(){[Name = "tData174"]}[Content], 
  ChgType = Table.TransformColumnTypes(Source, {{"From Date", type date}, {"To Date", type date}}), 
  Transform = Table.TransformRows(
    ChgType, 
    each 
      let
        E = [Emp], 
        DStart = [From Date], 
        DEnd = [To Date], 
        SalesPerDay = [Sales] / (Duration.Days(DEnd - DStart) + 1), 
        SDates = List.Generate(
          () => DStart, 
          each _ <= DEnd, 
          each Date.AddDays(Date.EndOfMonth(_), 1)
        ), 
        NewRowsValues = List.Accumulate(
          SDates, 
          [v = {}, rt = 0], 
          (s, c) =>
            let
              _ds     = c, 
              _de     = List.Min({Date.EndOfMonth(c), DEnd}), 
              _MSales = (Duration.Days(_de - _ds) + 1) * SalesPerDay, 
              _RT     = s[rt] + _MSales
            in
              [
                v  = s[v] & {{E, _ds, _de, _MSales, _RT}}, 
                rt = _RT * Number.From(Date.Month(_ds) <> 12)
              ]
        )
      in
        NewRowsValues[v]
  ), 
  Result = Table.FromRows(
    List.Combine(Transform), 
    {"Emp", "From Date", "To Date", "Monthly Sales", "Running Total"}
  )
in
  Result

let
 S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 A = Table.TransformColumnTypes(S,{{"From Date", type date}, {"To Date", type date}, {"Sales", Int64.Type}}),
 B = Table.AddColumn(A, "DL", each List.Dates([From Date], Duration.TotalDays([To Date]-[From Date])+1, hashtag#duration(1, 0, 0, 0))),
 C = Table.AddColumn(B, "SPD", each [Sales]/List.Count([DL])),
 D = Table.ExpandListColumn(C, "DL"),
 E = Table.AddColumn(D, "Month Name", each Date.MonthName([DL]), type text),
 E1 = Table.AddColumn(E, "Year", each Date.Year([DL]), Int64.Type),
 G = Table.Group(E1, {"Emp", "Month Name", "Year"}, {{"From", each List.Min([DL]), type date}, {"To", each List.Max([DL]), type date}, {"SPD", each List.Average([SPD]), type number}, {"DayNo", each Table.RowCount(_), Int64.Type}}),
 H = Table.AddColumn(G, "MonthlySales", each [SPD] * [DayNo], type number),
 I = Table.SelectColumns(H,{"Emp","Year", "From", "To", "MonthlySales"}),
 J = Table.Group(I, {"Emp", "Year"}, {{"Tbl", each _, type table [Emp=text, Year=number, From=date, To=date, MonthlySales=number]}}),
 


                    
                  
          

let
 F1 = Table.AddIndexColumn(X, "I", 1, 1, Int64.Type),
 F2 = Table.TransformColumns(F1,{{"MonthlySales", each Number.Round(_, 2), type number}}),
 F3 = Table.AddColumn(F2, "RunningSales", each List.Sum(List.FirstN(F2[MonthlySales],[I]))),
 F4 = Table.SelectColumns(F3,{"Emp", "From", "To", "MonthlySales", "RunningSales"})
in
 F4,
 K = Table.AddColumn(J, "F", each F([Tbl])),
 L = Table.SelectColumns(K,{"F"}),
 M = Table.ExpandTableColumn(L, "F", {"Emp", "From", "To", "MonthlySales", "RunningSales"}, {"Emp", "From", "To", "MonthlySales", "RunningSales"})
in
 M


                    
                  
          

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Dates = Table.AddColumn(Table.TransformColumnTypes(Source, {{"From Date", type date}, {"To Date", type date}}), "Dates", each List.Dates([From Date], Duration.Days([To Date] - [From Date]) + 1, hashtag#duration(1,0,0,0))),
 DailyS = Table.AddColumn(Dates, "DailySales", each [Sales] / List.Count([Dates])),
 ExpandDates = Table.ExpandListColumn(DailyS, "Dates"),
 EoM = Table.TransformColumns(Table.DuplicateColumn(ExpandDates, "Dates", "EOM"), {{"EOM", Date.EndOfMonth, type date}}),
 Year = Table.AddColumn(EoM, "Year", each Date.Year([EOM])),
 Group = Table.Group(Year, {"Emp", "EOM", "Year"}, {{"From Date", each List.Min([Dates]), type date}, {"To Date", each List.Max([Dates]), type date}, {"Monthly Sales", each List.Sum([DailySales]), type number}}),
 GroupYear = Table.Group(Group, {"Emp", "Year"}, {{"RT", each let a = [Monthly Sales], b = List.Transform({1..List.Count(a)}, each List.Sum(List.FirstN(a,_))), c = Table.FromColumns(List.Skip(Table.ToColumns(_), 3)&{b}, List.Skip(Table.ColumnNames(_), 3)&{"RT"}) in c}})
in
 Table.RemoveColumns(Table.ExpandTableColumn(GroupYear, "RT", Table.ColumnNames(GroupYear[RT]{0})), "Year")



                    
                  
          

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 ChangedType = Table.TransformColumnTypes(Source,{{"From Date", type date}, {"To Date", type date}}),
 TotalDays = Table.AddColumn(ChangedType, "TotalNumberOfDays", each Number.Abs(Duration.Days([From Date] - [To Date])-1)),
 AddedColumn = Table.AddColumn(TotalDays, "TableResult", each 
 let
 StartDate = [From Date],
 EndDate = [To Date],
 TotalDays = [TotalNumberOfDays],
 Sales = [Sales],
 TableWithDates = Table.FromRecords(List.Generate(
 () => [Emp = [Emp], DateFrom = [From Date], ToDate = Date.EndOfMonth(StartDate), DaysInMonth = Number.From(ToDate - DateFrom)+1, 
 MonthlySales = Number.Round(Number.From((DaysInMonth/TotalDays)) * [Sales],2), Total = MonthlySales, Year = Date.Year(ToDate)],
 each [DateFrom] <= EndDate,
                    
                  
          

 ToDate = if Date.Month(DateFrom) = Date.Month(EndDate) then EndDate else Date.EndOfMonth(DateFrom),
 DaysInMonth = Number.From(ToDate - DateFrom)+1, 
 MonthlySales = Number.Round(Number.From((DaysInMonth/TotalDays)) * Sales,2),
 Year = Date.Year(ToDate),
 Total = if Year <> [Year] then MonthlySales else MonthlySales + [Total]
 ],
 each [
 Emp = [Emp], 
 From Date = [DateFrom],
 To Date = [ToDate],
 Monthly Sales = [MonthlySales],
 Year = [Year],
 Running Total = [Total]
 ]))
 in TableWithDates
 ),
 TableOutput = Table.SelectColumns(AddedColumn,{"TableResult"}),
 ExpandedTable = Table.ExpandTableColumn(TableOutput, "TableResult", {"Emp", "From Date", "To Date", "Monthly Sales", "Year", "Running Total"})
 in 
 ExpandedTable
                    
                  

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.ToRecords(Table.TransformColumnTypes(Source,{{"Emp", type text}, {"From Date", type date}, {"To Date", type date}, {"Sales", Int64.Type}})),
 Custom1 = Table.Combine(List.Transform(#"Changed Type", each let 
 a=Duration.Days([To Date]-[From Date])+1, 
 b=[Sales]/a,
 c=List.Transform({Int64.From([From Date])..Int64.From([To Date])},Date.From),
 emp=List.Repeat({[Emp]},a),
 d=List.Transform(c,Date.MonthName),
 e=List.Transform(c,Date.Year), 
 f=Table.FromColumns({emp,c,d,e},type table [Emp=text,Date=date,Month=text,Year=number]),
 g=Table.Group(f,{"Year","Month"},{{"Emp", each [Emp]{0}},{"From", each List.Min([Date])},{"To", each List.Max([Date])}}),
 h=Table.AddColumn(g,"Sales",(r)=>(Duration.Days(r[To]-r[From])+1)*b) 
 in h)),



                    
                  
          

let b=List.Buffer({Custom1[Emp],Custom1[Year],Custom1[Sales]}), l=List.Generate(()=> 
 [i=0,rt=b{2}{i},y=b{1}{i},e=b{0}{i}], each [i][y] or e<>[e]) then b{2}{i} else [rt]+(b{2}{i}),y=b{1}{i},e=b{0}{i}], each [rt]) in Table.FromColumns(List.Skip(Table.ToColumns(Custom1),2)&{l}, type table [Emp=text,From=date,To=date,Sales=number,Running Total=number]),
 #"Rounded Off" = Table.TransformColumns(Custom2,{{"Sales", each Number.Round(_, 2,RoundingMode.AwayFromZero), type number}, {"Running Total", each Number.Round(_, 2,RoundingMode.AwayFromZero), type number}})
in
 #"Rounded Off"
                    
                  
          

=VSTACK({"Emp"."From Date"."To Date"."Monthly Sales"."Running Total"},LET(T,A2:D5,E,CHOOSECOLS(T,1),F,CHOOSECOLS(T,2),To,CHOOSECOLS(T,3),S,CHOOSECOLS(T,4),DDiff,To-F,DD,MONTH(To)-MONTH(F)+1,DDD,IF(DD<0,DD+12,DD),RDDD,SCAN(0,DDD,LAMBDA(A,B,A+B)),TM,MAKEARRAY(MAX(RDDD),4,LAMBDA(A,B,IFS(B=1,XLOOKUP(A,RDDD,E,,1),AND(B=2,A=1),INDEX(F,1),AND(B=2,A=XLOOKUP(A,RDDD,RDDD,,-1)+1),XLOOKUP(A,RDDD,F,,1),AND(B=3,ISNUMBER(XLOOKUP(A,RDDD,0))),INDEX(To,XMATCH(A,RDDD,-1)),B=4,XLOOKUP(A,RDDD,S,,1)/(XLOOKUP(A,RDDD,DDiff,INDEX(DDiff,1),1)+1)))),Emp,TAKE(TM,,1),RD,SCAN(0,CHOOSECOLS(TM,2),LAMBDA(A,B,IFERROR(B,EOMONTH(A,0)+1))),RDD,MAP(CHOOSECOLS(TM,3),RD,LAMBDA(T,F,IFERROR(T,EOMONTH(F,0)))),MS,IFERROR(CHOOSECOLS(TM,4)*(RDD-RD+1),INDEX(S,1)/(INDEX(To,1)-INDEX(F,1)+1)*(EOMONTH(INDEX(F,1),0)-INDEX(F,1)+1)),TTM,HSTACK(Emp,RD,RDD,MS,SCAN(0,SEQUENCE(MAX(RDDD)),LAMBDA(A,B,IF(AND(YEAR(INDEX(RD,B))=YEAR(INDEX(RD,B-1)),INDEX(Emp,B)=INDEX(Emp,B-1)),A+INDEX(MS,B),INDEX(MS,B))))),TTM))

=REDUCE(F1:J1,D2:D5,LAMBDA(x,y,LET(a,@+A5:y,b,@+B5:y,c,@+C5:y,d,SEQUENCE(c-b+1,,b),e,MONTH(d),f,DROP(GROUPBY(HSTACK(XMATCH(e,e),e),d,HSTACK(MIN,MAX),,0),1,2),g,ROUND(y/(c-b+1)*(BYROW(f*{-1,1},SUM)+1),2),
k,YEAR(TAKE(f,,1)),h,SEQUENCE(ROWS(k)),VSTACK(x,IFNA(HSTACK(a,f,g,MMULT((k=TOROW(k))*(h>=TOROW(h)),g)),a)))))

=REDUCE(
 {"Emp", "From Date", "To Date", "Monthly Sales", "Running Total"},
 SEQUENCE(ROWS(A2:A5)),
 LAMBDA(x, y,
 VSTACK(
 x,
 LET(
 _c1, INDEX(A2:D5, y, 1),
 _c2, INDEX(A2:D5, y, 2),
 _c3, INDEX(A2:D5, y, 3),
 _c4, INDEX(A2:D5, y, 4),
 _m, SEQUENCE(DATEDIF(_c2, EOMONTH(_c3, 0), "m") + 1, , 0),
 _d, EOMONTH(_c2, SEQUENCE(DATEDIF(_c2, EOMONTH(_c3, 0), "m") + 1, , 0)),
 _ed, IF(_d < _c3, _d, _c3),
 _sd, VSTACK(_c2, DROP(_ed + 1, -1)),
 _nd, _ed - _sd + 1,
 _s, _nd * _c4 / SUM(_nd),
 _ts, SCAN(
 0,
 SEQUENCE(ROWS(_sd)),
 LAMBDA(a, v, (MONTH(INDEX(_sd, v)) <> 1) * a + INDEX(_s, v))
 ),
 IFNA(HSTACK(_c1, _sd, _ed, _s, _ts), _c1)
 )
 )
 )
)

=LET(u,A2:A5,v,B2:B5,w,C2:C5,z,D2:D5,I,INDEX,s,SEQUENCE(ROWS(u)),
hdr,HSTACK(A1,B1,C1,"MOnthly Sales","Running Total"),
result,REDUCE(hdr,s,LAMBDA(x,y,LET(
 a,I(u,y),b,I(v,y),c,I(w,y),d,I(z,y),
 sq,SEQUENCE(c-b+1,,b),
 em,UNIQUE(EOMONTH(sq,0)),
 e,VSTACK(TAKE(sq,1),FILTER(sq,DAY(sq)=1)),
 f,TAKE(sq,-1),
 g,IF(TAKE(e,-1)=f,f,TAKE(sq,-1)),
 hd,VSTACK(DROP(em,-1),g),
 dt,HSTACK(e,hd),
 n,BYROW(dt,LAMBDA(x,SUM(DAY(x)*{-1,1},1))),
 amt,MAP(n,LAMBDA(x,ROUND(d*x/SUM(n),2))),
 rnt,DROP(REDUCE("",UNIQUE(YEAR(TAKE(dt,,1))),LAMBDA(j,k,VSTACK(j,SCAN(0,FILTER(amt,YEAR(TAKE(dt,,1))=k),SUM)))),1),
 ans,IFNA(HSTACK(a,dt,amt,rnt),a),
 res,VSTACK(x,ans),res))),
result)

=LET(t,"mm/dd/yyyy",l,TEXTSPLIT(TEXTJOIN(",",,MAP(A2:A5,B2:B5,C2:C5,LAMBDA(a,b,c,TEXTJOIN(",",,TOCOL(TEXTSPLIT(TEXTJOIN(",",,SEQUENCE((c-b+1),,b,1)&",&"&a),",")))))),,","),j,IF({"A","B","C","D"}=l,1,0),p,DATEVALUE(TEXT(FILTER(l,BYROW(j,LAMBDA(d,SUM(d)))=0),t)),r,FILTER(l,BYROW(j,LAMBDA(d,SUM(d)))=1),w,RIGHT(UNIQUE(r&TEXT(p,"mmm")),3),v,LEFT(UNIQUE(FILTER(l,BYROW(j,LAMBDA(d,SUM(d)))=1)&TEXT(DATEVALUE(TEXT(FILTER(l,BYROW(j,LAMBDA(d,SUM(d)))=0),t)),"mmm")),1),s,TEXT(p,"mmm"),y,MAP(w,v,LAMBDA(a,b,MAX(FILTER(p,((b=r)*(a=s))=1)))),x,MAP(w,v,LAMBDA(a,b,MIN(FILTER(p,((b=r)*(a=s))=1)))),q,DATEVALUE(TEXT(FILTER(l,BYROW(j,LAMBDA(d,SUM(d)))=0),t)),m,j,HSTACK(LEFT(UNIQUE(FILTER(l,BYROW(m,LAMBDA(d,SUM(d)))=1)&TEXT(DATEVALUE(TEXT(FILTER(l,BYROW(m,LAMBDA(d,SUM(d)))=0),t)),"mmm")),1),x,MAP(w,v,LAMBDA(a,b,MAX(FILTER(q,((b=r)*(a=TEXT(q,"mmm")))=1)))),MAP(v,LAMBDA(j,FILTER(D2:D5,A2:A5=j)))/MAP(v,LAMBDA(i,SUM(IF(i=r,1,0))))*(y-x+1),VALUE(TEXTSPLIT(TEXTJOIN(",",,BYCOL(VALUE(IFERROR(DROP(TRANSPOSE(TEXTSPLIT(TEXTJOIN(,,BYCOL(TOROW(UNIQUE(v)),LAMBDA(w,TEXTJOIN(",",,FILTER(MAP(v,LAMBDA(j,FILTER(D2:D5,A2:A5=j)))/MAP(v,LAMBDA(i,SUM(IF(i=r,1,0))))*(y-x+1),w=v))))&"/"),",","/")),,-1),0)),LAMBDA(r,TEXTJOIN(",",,UNIQUE(SCAN(0,r,LAMBDA(p,o,SUM(p,o)))))))),,","))))

Part 1: Import Data:
import pandas as pd
import xlwings as xw
import numpy as np
import datetime as dt
wb = xw.Book(r'C:UsersLukeDownloadsPQ_Challenge_174 (1).xlsx')
sh = wb.sheets['Sheet1']
table = sh.tables['Table1']
rng = sh.range(table.range.address)
df = rng.options(pd.DataFrame, header = True, index=False, numbers=int).value
                    
                  

df_input = xl("A1:D5", headers=True)
def pad(df, date_col, freq='D'):
 return df.assign(**{date_col: pd.to_datetime(df[date_col])})
 .set_index(date_col).asfreq(freq).reset_index()
res = (
 df_input.melt(id_vars=["Emp", "Sales"], var_name="date", value_name="value")
 .sort_values(["Emp", "value"])
 .groupby("Emp", group_keys=False)
 .apply(lambda x: pad(x, "value"))
 .ffill()
 .assign(
 days=lambda x: x.groupby("Emp")["value"].transform("count"),
 daily_sales=lambda x: x["Sales"] / x["days"],
 month=lambda x: x["value"].dt.to_period("M").dt.to_timestamp(),
 year=lambda x: x["value"].dt.year,
 )
 .groupby(["Emp", "month", "year"], as_index=False)
 .agg(Monthly_Sales=("daily_sales", "sum"), From_Date=("value", "min"), To_Date=("value", "max"))
 .assign(Running_Total=lambda x: x.groupby(["Emp", "year"])["Monthly_Sales"].cumsum())
 .round({"Monthly_Sales": 2, "Running_Total": 2})
)[["Emp", "From_Date", "To_Date", "Monthly_Sales", "Running_Total"]]
res
                    
                  

library(readxl)
library(janitor)
library(tidyverse)
df <- read_xlsx("PQ/PQ_Challenge_174.xlsx", range = cell_cols(LETTERS[1:4]))
df %>% 
 clean_names() %>% 
 pivot_longer(contains("date"), values_to = "dates", names_to = NULL) %>% 
 mutate(dates = as.Date(dates)) %>% 
 group_by(emp) %>% 
 mutate(from_date = floor_date(dates, "month")) %>% 
 complete(from_date = seq.Date(min(from_date), max(from_date), "month")) %>% 
 fill(sales) %>% 
 mutate(from_date = case_when(row_number() == 1 ~ dates,
 TRUE ~ from_date),
 to_date = case_when(row_number() == n() ~ dates,
 TRUE ~ ceiling_date(from_date, "month") - days(1))) %>% 
 select(-dates) %>% 
 mutate(month_sales = sales * as.numeric(to_date - from_date + 1)/as.numeric(sum(to_date - from_date + 1))) %>% 
 ungroup() %>% 
 group_by(emp, Year = year(from_date)) %>% 
 mutate(running_sales = cumsum(month_sales)) %>% 
 ungroup() %>% 
 select(Emp = emp, `From Date` = from_date, `To Date` = to_date, `Monthly Sales` = month_sales, `Running Total` = running_sales)