Monthly Sales and Workdays Summary

Populate the total sales for the companies and no. of workdays for months in the year (only month is important not year) Also provide Row and column totals. Sort on the basis of Company.

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

Solving the challenge of Monthly Sales and Workdays Summary with Power Query

Power Query solution 1 for Monthly Sales and Workdays Summary, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table5"]}[Content], 
  Mlist = List.Transform({1 .. 12}, each Date.ToText(Date.AddMonths(Date.From(1), _), "MMM")), 
  Work = Table.AddColumn(
    Source, 
    "W", 
    each Table.Group(
      Table.FromColumns(
        {
          List.RemoveNulls(
            List.Transform(
              {Number.From([From]) .. Number.From([To])}, 
              each if Number.Mod(_, 7) > 1 then Date.ToText(Date.From(_), "MMM") else null
            )
          )
        }, 
        {"Name"}
      ), 
      "Name", 
      {"Value", each Table.RowCount(_)}
    )
  ), 
  Group = Table.AddColumn(
    Table.ExpandTableColumn(
      Table.Group(
        Work, 
        {"Company"}, 
        {
          {"Sales", each List.Sum([Sales])}, 
          {
            "T", 
            each 
              let
                w = Table.Combine([W])
              in
                Table.Pivot(w, Mlist, "Name", "Value", List.Sum)
          }
        }
      ), 
      "T", 
      Mlist
    ), 
    "Row Total", 
    each List.Sum(List.Skip(Record.ToList(_), 2))
  ), 
  Total = Table.Sort(Group, {"Company"})
    & Table.FromRows(
      {
        List.Transform(
          Table.ToColumns(Table.Sort(Group, {"Company"})), 
          each try List.Sum(_) otherwise "Column Total"
        )
      }, 
      Table.ColumnNames(Group)
    )
in
  Total

Power Query solution 2 for Monthly Sales and Workdays Summary, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
 Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
 Calculos = Table.AddColumn(Source, "Custom", each 
let
a = Date.From([To]-[From]),
b = {Number.From([From])..Number.From([To])},
c = List.Transform(b, each Date.From(_)),
d = List.Select(c, each Date.DayOfWeekName(_) <> "Saturday" and Date.DayOfWeekName(_) <> "Sunday" ),
e = Table.FromList(d, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
f = Table.TransformColumns(e,{{"Column1", Date.MonthName}}),
g = Table.Group(f, {"Column1"}, {{"Count", each Table.RowCount(_)}}),
h = Table.Sort(g,{{"Column1", Order.Ascending}})
in h
),
 Expand = Table.ExpandTableColumn(Calculos, "Custom", {"Column1", "Count"}),
 Month = Table.TransformColumns(Expand, {{"Column1", each Text.Start(_, 3), type text}}),

Power Query solution 3 for Monthly Sales and Workdays Summary, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  tb = Table.AddColumn(
    Fonte, 
    "Data", 
    each List.Transform({Number.From(Date.From([From])) .. Number.From(Date.From([To]))}, Date.From)
  ), 
  exp = Table.ExpandListColumn(tb, "Data"), 
  add = Table.AddColumn(
    exp, 
    "Personalizar", 
    each [
      a = Number.Mod(Number.From([Data]), 7), 
      b = Text.Start(Date.MonthName([Data]), 3), 
      c = Date.Month([Data])
    ]
  ), 
  ex = Table.ExpandRecordColumn(add, "Personalizar", {"a", "b", "c"}), 
  fil = Table.SelectRows(ex, each ([a] <> 0 and [a] <> 1)), 
  gp = Table.Group(fil, {"Company", "b", "c"}, {{"Contagem", each Table.RowCount(_), Int64.Type}}), 
  class = Table.Sort(gp, {{"Company", Order.Ascending}, {"c", Order.Ascending}})[
    [Company], 
    [b], 
    [Contagem]
  ], 
  Sum = Table.Combine(
    {
      Table.AddColumn(
        Table.Group(Fonte, {"Company"}, {{"Contagem", each List.Sum([Sales]), type number}}), 
        "b", 
        each "Sales"
      ), 
      class
    }
  ), 
  pv = Table.Pivot(Sum, List.Distinct(Sum[b]), "b", "Contagem"), 
  tab = Table.AddColumn(pv, "Row Total", each List.Sum(List.RemoveFirstN(Record.FieldValues(_), 2))), 
  res = Table.Combine(
    {
      tab, 
      Table.FromRows(
        {{"Column Total"} & List.Transform(List.RemoveFirstN(Table.ToColumns(tab), 1), List.Sum)}, 
        Table.ColumnNames(tab)
      )
    }
  )
in
  res

Power Query solution 4 for Monthly Sales and Workdays Summary, proposed by Alexis Olson:

let
 Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
 ChangeType = Table.TransformColumnTypes(Source,{{"From", type date}, {"To", type date}}),
 AddMonthList = Table.AddColumn(ChangeType, "Month", each
 let
 DateList = List.Dates([From], Duration.Days([To]-[From])+1, hashtag#duration(1,0,0,0)),
 Weekdays = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
 Months  = List.Transform(Weekdays, each Date.ToText(_, "MMM"))
 in
 Months
 ),
 ExpandMonthList = Table.ExpandListColumn(AddMonthList, "Month"),
 GroupRows = Table.Group(ExpandMonthList, {"Company", "Month"}, {{"Count", each Table.RowCount(_)}}),
 GroupTotal = Table.Group(GroupRows, {"Month"}, {{"Count", each List.Sum([Count])}, {"Company", each "zzColumn Total"}}),
 AppendTotal = Table.Combine({GroupRows, GroupTotal}),
 PivotMonths = Table.Pivot(AppendTotal, List.Distinct(AppendTotal[Month]), "Month", "Count"),
 AddRowTotal = Table.AddColumn(PivotMonths, "Row Total", each List.Sum(List.Skip(Record.ToList(_)))),
 ReplaceValue = Table.ReplaceValue(AddRowTotal,"zzColumn","Column",Replacer.ReplaceText,{"Company"})
in
 ReplaceValue

Power Query solution 5 for Monthly Sales and Workdays Summary, proposed by Owen Price:

Nonetheless, I hope it's interesting to others. 
https://gist.github.com/ncalm/aab7c59371d70e4668cf9d3b6738d338

Solving the challenge of Monthly Sales and Workdays Summary with Excel

Excel solution 1 for Monthly Sales and Workdays Summary, proposed by Bo Rydobon 🇹🇭:

=LET(z,SORT(Table5),s,SEQUENCE(,12),
r,REDUCE(HSTACK(A1,D1,TEXT("1/"&s,"mmm"),"Row Total"),SEQUENCE(ROWS(z)),LAMBDA(a,n,LET(f,INDEX(z,n,2),
d,SEQUENCE(INDEX(z,n,3)-f+1,,f),w,IF(MOD(d,7)>1,MONTH(d)),c,INDEX(z,n,1),
m,MAP(s,LAMBDA(m,SUM(N(w=m)))),o,HSTACK(INDEX(z,n,4),m,SUM(m)),
IF(INDEX(a,ROWS(a),1)=c,VSTACK(DROP(a,-1),HSTACK(c,TAKE(a,-1,-14)+o)),
VSTACK(a,HSTACK(INDEX(z,n,1),o)))))),
VSTACK(r,HSTACK("Column Total",MMULT(SEQUENCE(,ROWS(r)-1)^0,DROP(r,1,1)))))

Excel solution 2 for Monthly Sales and Workdays Summary, proposed by محمد حلمي:

=LET(s,SEQUENCE(,12),e,A2:A7,u,UNIQUE(e),i,HSTACK(SUMIF(e,u,D2:D7),DROP(
REDUCE(0,u,LAMBDA(p,k,VSTACK(p,
BYCOL(FILTER(DROP(
REDUCE(0,B2:B7,LAMBDA(n,b,LET(r,SEQUENCE(OFFSET(b,,1)-b+1,,b),VSTACK(n,
MAP(s,LAMBDA(a,SUM((MONTH(r)=a)*NETWORKDAYS(r,r)))))))),1),e=k),LAMBDA(a,SUM(a)))))),1), MAP(u,LAMBDA(a,SUM((e=a)*NETWORKDAYS(+B2:B7,+C2:C7))))),VSTACK(HSTACK(A1,D1,TEXT(s*29,"mmm"),"Row Total"),
IFNA(HSTACK(u,VSTACK(i,
BYCOL(i,LAMBDA(a,SUM(a))))),"Column Total")))

Excel solution 3 for Monthly Sales and Workdays Summary, proposed by محمد حلمي:

=LET(e,A2:A7,u,UNIQUE(e),i,HSTACK(SUMIF(e,u,D2:D7),DROP(REDUCE(0,UNIQUE(e),LAMBDA(ee,rr,VSTACK(ee,BYCOL( FILTER(DROP(REDUCE(0,B2:B7,LAMBDA(n,b, LET(r,SEQUENCE(OFFSET(b,,1)-b+1,,b),VSTACK(n,MAP(SEQUENCE(,12),
LAMBDA(a,SUM((MONTH(r)=a)*NETWORKDAYS(r,r)))))))),1),e=rr),LAMBDA(a,SUM(a)))))),1), MAP(u,LAMBDA(a,SUM((e=a)*NETWORKDAYS(+B2:B7,+C2:C7))))),nn,VSTACK(HSTACK(A1,D1,
TEXT(SEQUENCE(,12)*29,"mmm"),"Row Total"),IFNA(HSTACK(u,VSTACK(i,BYCOL(i,LAMBDA(a,SUM(a))))),
"Column Total")),IF(nn>0,nn,""))

Excel solution 4 for Monthly Sales and Workdays Summary, proposed by 🇰🇷 Taeyong Shin:

=LET(
 F, LAMBDA(x,
 LET(
 s, INDEX(x, 2),
 d, SEQUENCE(INDEX(x, 3) - s + 1, , s),
 g, HSTACK(0, GROUPBY(HSTACK(MONTH(d), TEXT(d, "mmm")), GESTEP(-WEEKDAY(d, 2), -5), SUM, , 0)),
 CHOOSE({1,2,2,2,3}, @+x, g, TAKE(x, , -1) / ROWS(g))
 )
 ),
 t, DROP(REDUCE(0, A2:A7, LAMBDA(a,v, VSTACK(a, F(TAKE(D7:v, 1))))), 1),
 g, GROUPBY(VSTACK("Company", TAKE(t, , 1)), VSTACK("Sales", TAKE(t, , -1)), SUM, 3),
 HSTACK(g, DROP(PIVOTBY(TAKE(t, , 1), CHOOSECOLS(t, 2, 3), INDEX(t, , 4), SUM), 1, 1))
)

Excel solution 5 for Monthly Sales and Workdays Summary, proposed by Oscar Mendez Roca Farell:

=LET(_d, B2:C7,_u, ORDER(UNIQUE(A2:A7)),_r, REDUCE(HSTACK("Sales", TEXT(DATE(YEAR(MIN(_d)),SEQUENCE(,12),1),"mmm")),_u, LAMBDA(k, z, LET(_m, DROP(REDUCE("",SEQUENCE(ROWS(_d)),LAMBDA(i, x, VSTACK(i, LET(_f, DATE(TOCOL(YEAR(INDEX(_d, x, ))),SEQUENCE( ,12),1), REDUCE(0,{1,2}, LAMBDA(j, y, j + BYCOL(INDEX(_f, y ,), LAMBDA(c, MAX( , NETWORKDAYS(MAX(MIN(INDEX(_d, x, )),c), MIN(MAX(INDEX(_d, x, )), EOMONTH(c,0))))))))) / IF(SUM(YEAR(INDEX(_d, x, ))*{-11}),1,2)))),1), VSTACK(k, BYCOL(FILTER(HSTACK(D2:D7,_m), A2:A7=z), LAMBDA(t, SUM(t))))))),_tc, MMULT({111},DROP(_r,1)),_tf, MMULT(DROP(_r,1,1),SEQUENCE(12)^0), IFNA(VSTACK(HSTACK(VSTACK("Company", _u),_r, VSTACK("Row Total",_tf )), HSTACK("Column Total",_tc )),SUM(_tf )))

Excel solution 6 for Monthly Sales and Workdays Summary, proposed by LEONARD OCHEA 🇷🇴:

=LET(comp,SORT(UNIQUE(Table5[Company])),mx,MAX(Table5[[From]:[To]]),mn,MIN(Table5[[From]:[To]]),mo,SEQUENCE(,12),td,SEQUENCE(,mx-mn+1,mn),m,(td>=Table5[From])*(td<=Table5[To])*(WEEKDAY(td,3)<5),mm,(TOCOL(mo)=MONTH(td))*1,mmm,MMULT(mm,TRANSPOSE(m)),c,(TOROW(comp)=Table5[Company])*1,df,TRANSPOSE(MMULT(mmm,c)),ts,SUMIF(Table5[Company],comp,Table5[Sales]),tdf,BYROW(df,LAMBDA(a,SUM(a))),ah,HSTACK(ts,df,tdf),th,BYCOL(ah,LAMBDA(b,SUM(b))),nm,TEXT("1/"&mo,"mmm"),HSTACK(VSTACK("Company",comp,"Column Total"),VSTACK(HSTACK("Sales",nm,"Row Total"),ah,th)))

Solving the challenge of Monthly Sales and Workdays Summary with Excel VBA

Excel VBA solution 1 for Monthly Sales and Workdays Summary, proposed by Vasin Nilyok:

VBA 1/3
Sub PQChallenge68()
Dim ComCollection As New Collection, MonthCollection As New Collection
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
StartCol = 22
Cells(1, StartCol) = "Company"
Cells(1, StartCol + 1) = "Sales"
For c = 1 To 12
 Mo = WorksheetFunction.EDate(1, c - 1)
 Cells(1, c + StartCol + 1) = WorksheetFunction.Text(Mo, "mmm")
Next c
Cells(1, StartCol + 12 + 2) = "Row Total"
For r = 2 To LastRow
 On Error Resume Next
 ComCollection.Add Cells(r, 1).Value, CStr(Cells(r, 1).Value)
Next r
nCom = ComCollection.Count
For r = 2 To nCom + 1
 Cells(r, StartCol) = ComCollection(r - 1)
 nSales = WorksheetFunction.SumIfs(Columns("d"), Columns("a"), Cells(r, StartCol).Value)
 Cells(r, StartCol + 1) = nSales
Next r
Set rngComName = Range(Cells(2, StartCol), Cells(nCom + 1, StartCol + 1))
rngComName.Sort key1:=Cells(2, StartCol), order1:=xlAscending
Set rngComName = Range(Cells(2, StartCol), Cells(nCom + 1, StartCol))
nDay = 0
AccDay = 0
MonthFillCol = 24 'to 35
For r = 2 To LastRow
 StartDate = Cells(r, 2)
 EndDate = Cells(r, 3)
 ComRow = WorksheetFunction.Match(Cells(r, 1).Value, rngComName, 0) + 1

&&&

Solving the challenge of Monthly Sales and Workdays Summary with Power Query

Solving the challenge of Monthly Sales and Workdays Summary with Excel

Solving the challenge of Monthly Sales and Workdays Summary with Excel VBA

Leave a Reply Cancel reply