Sum the total sales per Week Excluding Weekends Dynamic array function allowed but Extra marks for Legacy Array Functions or PowerQuery Solution
📌 Challenge Details and Links
Challenge Number: 17
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum Data Per Week Excluding Weekends with Power Query
Power Query solution 1 for Sum Data Per Week Excluding Weekends, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = Table.SelectRows(Source, each Date.DayOfWeek([Date]) <> 0 and Date.DayOfWeek([Date]) <> 6),
From = Table.Sort(
Table.AddColumn(
Rows,
"From - To",
each Text.From(Date.From(Date.StartOfWeek([Date], 1)))
& " - "
& Text.From(Date.From(Date.EndOfWeek([Date], 1)))
),
each [Date]
),
Sol = Table.Group(From, {"From - To"}, {{"Total Sales", each List.Sum([Sales])}})
in
SolPower Query solution 2 for Sum Data Per Week Excluding Weekends, proposed by Brian Julius:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"Date", Date.Type}
),
Filter = Table.SelectRows(Source, each Text.Start(Date.DayOfWeekName([Date]), 1) <> "S"),
AddWeekToFrom = Table.AddColumn(
Filter,
"WeekFromTo",
each [
a = Date.StartOfWeek([Date], 1),
b = Date.AddDays(a, 6),
c = Text.From(a) & " - " & Text.From(b)
][c]
),
Group = Table.Group(
AddWeekToFrom,
{"WeekFromTo"},
{{"Total Sales", each List.Sum([Sales]), type nullable number}}
)
in
GroupPower Query solution 3 for Sum Data Per Week Excluding Weekends, proposed by Bhavya Gupta:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"dZK7DgIhEEV/xVCzYR7Iozb+gBYWGwsLjTaa6Pr/wsZhZRdLuHfODHPpe7V5nF6D0grJEBoConRw0amj7tXh/BrOz3u6gdCB65LMTTlXg1RHD6O8Pf3IvJBLZ+xgLehmLTmpRc1IM4M1ZM23vKVnQCgv0xB4NOwf7+G62t0ueYakszD+WDLGTxiy80G5Yiz1DLAC8No6WjaRHlxZ6j2vhRF09HbWBKgDlmW2DJlAJQodKDSfyrj0lMDgi+BKrqeMJTLQHuvEo8FQEpvkakicAJgcjV3lLtww/Swj/SyUZYjj+AE=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Region = _t, Date = _t, Sales = _t]
),
changedType = Table.TransformColumnTypes(
Source,
{{"Region", type text}, {"Date", type text}, {"Sales", Int64.Type}}
)
in
changedTypePower Query solution 4 for Sum Data Per Week Excluding Weekends, proposed by Nelson Mwangi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FilterWeekends = Table.SelectRows(
Source,
each Date.DayOfWeek([Date]) <> 0 and Date.DayOfWeek([Date]) <> 6
),
EndOfWeek = Table.AddColumn(
FilterWeekends,
"EndOfWeek",
each Date.From(Date.EndOfWeek([Date], 1))
),
Group = Table.Group(
EndOfWeek,
{"EndOfWeek"},
{{"WeekStart", each List.Min([Date])}, {"Sales", each List.Sum([Sales])}}
),
MergeCols = Table.AddColumn(
Group,
"Week Start-End",
each Text.Combine({Text.From([WeekStart]), Text.From([EndOfWeek])}, " - ")
),
Sol = Table.SelectColumns(MergeCols, {"Week Start-End", "Sales"})
in
SolPower Query solution 5 for Sum Data Per Week Excluding Weekends, proposed by 📊 Victor Ionescu:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Region", type text}, {"Date", type datetime}, {"Sales", Int64.Type}}
),
#"Inserted Day Name" = Table.AddColumn(
#"Changed Type",
"Day Name",
each Date.DayOfWeekName([Date]),
type text
),
#"Filtered Rows" = Table.SelectRows(
#"Inserted Day Name",
each ([Day Name] <> "duminică" and [Day Name] <> "sâmbătă")
),
#"Inserted Week of Year" = Table.AddColumn(
#"Filtered Rows",
"Week of Year",
each Date.WeekOfYear([Date]),
Int64.Type
),
#"Grouped Rows" = Table.Group(
#"Inserted Week of Year",
{"Week of Year"},
{{"Week sales", each List.Sum([Sales]), type nullable number}}
)
in
#"Grouped Rows"Solving the challenge of Sum Data Per Week Excluding Weekends with Excel
Excel solution 1 for Sum Data Per Week Excluding Weekends, proposed by Rick Rothstein:
=SUM(D$3:D$25*(C$3:C$25>=0+LEFT(
F3,
FIND(
"-",
F3)-2))*(C$3:C$25Excel solution 2 for Sum Data Per Week Excluding Weekends, proposed by Julian Poeltl:
=LET(Dat,
Table1[Date],
Sales,
Table1[Sales],
Y,
YEAR(
Dat),
WD,
WEEKDAY(
Dat,
2),
KW,
BYROW(
Dat,
LAMBDA(
ARR,
WEEKNUM(
ARR,
2))),
UYKW,
Y&KW,
USUM,
UNIQUE(
UYKW),
SUM,
BYROW(USUM,
LAMBDA(AR,
SUMPRODUCT(Sales*(WD<6)*(UYKW=AR)))),
SUM0,
FILTER(
SUM,
SUM>0),
KWBegin,
DATE(
LEFT(
USUM,
4)*1,
1,
7*RIGHT(
USUM,
LEN(
USUM)-4)-3-WEEKDAY(
DATE(
LEFT(
USUM,
4)*1,
,
),
3)),
KWBF,
FILTER(
KWBegin,
SUM>0),
KWB,
KWBF-7,
SUMSort,
SORTBY(
SUM0,
KWB,
1),
KWBSort,
SORT(
KWB),
HSTACK(
TEXT(
KWBSort,
"M/D/YYYY")&" - "&TEXT(
KWBSort+6,
"M/D/YYYY"),
SUMSort))Excel solution 3 for Sum Data Per Week Excluding Weekends, proposed by Hamidi Hamid:
=SOMME(
RECHERCHEX(
TEXTE.AVANT(
H3;
"-")*1;
MAP(
'Sales Data'!$C$3:$C$25;
LAMBDA(
a;
SI(
JOURSEM(
a;
2)<6;
a;
0)));
'Sales Data'!$D$3:$D$25;
0;
1;
1):RECHERCHEX(
TEXTE.APRES(
H3;
"-")*1;
MAP(
'Sales Data'!$C$3:$C$25;
LAMBDA(
a;
SI(
JOURSEM(
a;
2)<6;
a;
0)));
'Sales Data'!$D$3:$D$25;
0;
-1;
1))Excel solution 4 for Sum Data Per Week Excluding Weekends, proposed by Ankur Sharma:
=LET(a,
Table1[Date],
BYROW(F3:F10,
LAMBDA(b,
SUM(FILTER(Table1[Sales],
(a >= (--TEXTBEFORE(
b,
" ",
1))) * (a <= --TEXTBEFORE(
b,
" ",
1) + 4))))))Excel solution 5 for Sum Data Per Week Excluding Weekends, proposed by Mahmoud Bani Asadi:
=DROP(
GROUPBY(
WEEKNUM(
+tbl[Date],
2),
HSTACK(
tbl[Date],
tbl[Date],
tbl[Sales]),
HSTACK(
MIN,
MAX,
SUM),
,
,
2,
WEEKDAY(
+tbl[Date],
2)<6),
1,
1)Excel solution 6 for Sum Data Per Week Excluding Weekends, proposed by Dominic Walsh:
=LET(
a,
C3:C25,
b,
FILTER(
a,
MOD(
a-2,
7)<5),
c,
SORT(
UNIQUE(
b-MOD(
b-2,
7))),
d,
TEXT(
c,
"m/d/yyyy") & " - " & TEXT(
c+6,
"m/d/yyyy"),
e,
D3:D25,
f,
SUMIFS(
e,
a,
">="&c,
a,
"<"&c+5),
HSTACK(
d,
f))Excel solution 7 for Sum Data Per Week Excluding Weekends, proposed by William Kiarie:
=SUMIFS(
D:D,
C:C,
">=" & I3,
C:C,
"<=" & J3,
E:E,
"<=" & 5)
or
=SUM(FILTER(D:D,
(C:C>=I3)*(C:C<=J3)*(E:E<=5)))