Per day rates for every person for a duration is given. Find the total quarterly amount for all persons for Working days in that quarter. If To Date is blank, then that will mean 31-Dec. Taking example of Robert for Q2. Working days between 1-Apr to 14-May = 31. Working days between 15-May to 30-Jun = 34 Hence Q2 Total = 31*50 + 34*60 = 3590
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 100
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Quarterly Working Payment with Power Query
Power Query solution 1 for Calculate Quarterly Working Payment, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Q = Table.AddColumn(Source, "Q", each
List.Transform(List.Select(List.Transform({Number.From([From Date]).. Number.From([To Date] ?? hashtag#date(2022,12,31))},Date.From),each Date.DayOfWeek(_,1)<5),Date.QuarterOfYear)),
Qn = List.Accumulate({1,2,3,4}, Q, (s,l)=> Table.AddColumn(s, "Q"&Text.From(l), each List.Count(List.Select([Q],each _=l))*[Rate] )),
Group = Table.Group(Qn, "Name", {{"Q1", each List.Sum([Q1])},{"Q2", each List.Sum([Q2])},{"Q3", each List.Sum([Q3])},{"Q4", each List.Sum([Q4])}})
in
Group
Power Query solution 2 for Calculate Quarterly Working Payment, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Prep = Table.ExpandListColumn(Table.CombineColumns(Source, {"From Date", "To Date"}, each let i = List.Transform(_, each Date.From(_) ?? hashtag#date(2022, 12, 31)), d = List.Dates(i{0}, Duration.Days(i{1} - i{0}) + 1, Duration.From(1)) in List.Transform(List.Select(d, each Date.DayOfWeek(_, 1) < 5), each "Q" & Text.From(Date.QuarterOfYear(_))), "Q"), "Q"),
S = Table.Sort(Table.Pivot(Prep, List.Distinct(Prep[Q]), "Q", "Rate", List.Sum), each List.PositionOf(Source[Name], [Name]))
in
S
Power Query solution 3 for Calculate Quarterly Working Payment, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Nulls = Table.TransformColumns(Source, {{"From Date", each Date.From(_)},{"To Date", each if _ = null then hashtag#date(2022,12,31) else Date.From(_)}}),
Dates = Table.AddColumn(Nulls, "Dates", each let
a = {Number.From([From Date])..Number.From([To Date])},
b = List.Transform(a, Date.From)
in List.Select(b, each Date.DayOfWeek(_)<>6 and Date.DayOfWeek(_)<>0)),
Group = Table.Group(Dates, {"Name"}, {{"All", each
let
a = [[Rate],[Dates]],
b = Table.ExpandListColumn(a, "Dates"),
c = Table.AddColumn(b, "Q", each "Q"& Text.From(Number.IntegerDivide(Date.Month([Dates])-1,3)+1)),
d = Table.Group(c, {"Q"}, {{"Rate", each List.Sum([Rate])}})
in Table.PromoteHeaders(Table.Transpose(d))}}),
Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
Sol
Luan, utilicé tu técnica al final 👏
Power Query solution 4 for Calculate Quarterly Working Payment, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
sub = Table.ReplaceValue(Fonte,null,hashtag#datetime(2022, 12, 31, 0, 0, 0),Replacer.ReplaceValue,{"To Date"}),
trf = Table.AddColumn(sub, "Personalizar", each [
a = {Number.From(Date.From([From Date]))..Number.From(Date.From([To Date]))},
b = List.Transform(a,each Date.DayOfWeek(Date.From(_))),
c = List.Select(List.Zip({a,b}), each _{1} <> 0 and _{1} <> 6),
d = List.Transform(c, (x)=> Date.From(x{0})),
e = Table.Combine(List.Transform(d, (x)=> Table.FromList({"Q"&Text.From(Date.QuarterOfYear(x))})))
][e]),
exp = Table.ExpandTableColumn(trf, "Personalizar", {"Column1"}),
gp1 = Table.Group(exp, {"Name","Column1","Rate"}, {{"Contagem", each List.Count(_[Column1]) * [Rate]{0}}})[[Name],[Column1],[Contagem]],
res = Table.Sort(Table.Pivot(gp1, List.Distinct(gp1[Column1]), "Column1", "Contagem", List.Sum),each List.PositionOf(List.Distinct(sub[Name]),[Name]))
in
res
Power Query solution 5 for Calculate Quarterly Working Payment, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Rate", Int64.Type}}),
Add3112 = Table.ReplaceValue(#"Changed Type","", each "31/12/" & Text.End([From Date], 4),Replacer.ReplaceValue,{"To Date"}),
ListDates = Table.AddColumn(Add3112, "Custom", each List.Dates(Date.From([From Date]),
Number.From(Date.From([To Date]) - Date.From([From Date])) + 1, hashtag#duration(1, 0, 0, 0))),
Expand = Table.ExpandListColumn(ListDates, "Custom"),
AddQ = Table.AddColumn(Expand, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Custom])), Int64.Type),
DayName = Table.TransformColumns(AddQ, {{"Custom", each Date.DayOfWeekName(_), type text}}),
KeepWD = Table.SelectRows(DayName, each ([Custom] <> "Saturday" and [Custom] <> "Sunday")),
Group = Table.Group(KeepWD, {"Name", "Quarter"}, {{"sum", each List.Sum([Rate]), type nullable number}}),
PivotQ = Table.Pivot(Table.TransformColumnTypes(Group, {{"Quarter", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Group, {{"Quarter", type text}}, "en-GB")[Quarter]), "Quarter", "sum", List.Sum)
in PivotQ
Power Query solution 6 for Calculate Quarterly Working Payment, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FillNullDates = Table.AddColumn(Source, "ToDate", each if [To Date] = null then hashtag#datetime(Date.Year( [From Date]), 12,31,0,0,0) else [To Date]),
AddDateList = Table.AddColumn(FillNullDates, "DateList", each [
a = List.Transform( { Number.From(Date.From([From Date])).. Number.From(Date.From([ToDate]))}, each Date.From(_)),
b = List.Select( a, each not Text.StartsWith( Date.DayOfWeekName(_), "S")),
c = List.Transform(b, each "Q" & Text.From( Date.QuarterOfYear(_) )),
d = Table.FromColumns( {b,c} )
][d]),
Expand = Table.SelectColumns( Table.ExpandTableColumn(AddDateList, "DateList", {"Column1", "Column2"}, {"Column1", "Column2"}), {"Name", "Rate", "Column2"}),
Pivot = Table.Pivot(Expand, List.Distinct(Expand[Column2]), "Column2", "Rate", List.Sum)
in
Pivot
Power Query solution 7 for Calculate Quarterly Working Payment, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "RatesPerDay"]}[Content],
Type = Table.TransformColumnTypes(Source, {{"From Date", type date}, {"To Date", type date}}),
Ultimo = Table.ReplaceValue(
Type,
null,
each Date.EndOfYear([From Date]),
Replacer.ReplaceValue,
{"To Date"}
),
Days = Table.AddColumn(Ultimo, "Days", each {Int64.From([From Date]) .. Int64.From([To Date])})[
[Name],
[Rate],
[Days]
],
Expanded = Table.ExpandListColumn(Days, "Days"),
Type1 = Table.TransformColumnTypes(Expanded, {{"Days", type date}}),
Weekdays = Table.SelectRows(Type1, each (Date.DayOfWeek([Days]) < 5)),
Quarter = Table.TransformColumns(Weekdays, {{"Days", Date.QuarterOfYear, Int64.Type}}),
Grouped = Table.Group(
Quarter,
{"Name", "Rate", "Days"},
{{"Count", each List.Max([Rate]) * Table.RowCount(_), Int64.Type}}
)[[Name], [Days], [Count]],
Pivoted = Table.Pivot(
Table.TransformColumns(Grouped, {{"Days", each "Q" & Text.From(_, "de-DE"), type text}}),
List.Distinct(
Table.TransformColumns(Grouped, {{"Days", each "Q" & Text.From(_, "de-DE"), type text}})[Days]
),
"Days",
"Count",
List.Sum
)
in
PivotedPower Query solution 8 for Calculate Quarterly Working Payment, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
S1 = Table.TransformColumnTypes(Source,{{"From Date", type date}, {"To Date", type date}}),
S2 = Table.TransformColumns(S1,{"To Date",each _ ?? hashtag#date(2022,12,31)}),
S3 = Table.AddColumn(S2, "Custom", each List.Dates([From Date],1+Number.From([To Date])-Number.From([From Date]),hashtag#duration(1,0,0,0))),
S4 = Table.TransformColumns(S3,{"Custom",each Table.Group(Table.SelectRows(Table.FromList(_,each {_,Date.DayOfWeek(_),Date.QuarterOfYear(_)}),each [Column2] <> 0 and [Column2] <> 6 ), {"Column3"}, {{"Count", each Table.RowCount(_), Int64.Type}})}),
S5 = Table.ExpandTableColumn(S4, "Custom", {"Column3", "Count"}, {"Column3", "Count"}),
S6 = Table.TransformColumns(S5, {{"Column3", each "Q" & Text.From(_, "en-IN"), type text}}),
S7 = Table.AddColumn(S6, "Multiplication", each [Rate] * [Count], type number),
S8 = Table.RemoveColumns(S7,{"From Date", "To Date", "Rate", "Count"}),
S9 = Table.Pivot(S8, List.Distinct(S8[Column3]), "Column3", "Multiplication", List.Sum)
in
S9
Power Query solution 9 for Calculate Quarterly Working Payment, proposed by Henriette Hamer:
let
Source = Input,
RV1 = Table.ReplaceValue(Source,null,hashtag#date(2022, 12, 31),Replacer.ReplaceValue,{"To Date"}),
CT1 = Table.TransformColumnTypes(RV1,{{"From Date", Int64.Type}, {"To Date", Int64.Type}}),
AC1 = Table.AddColumn(CT1, "ListOfDates", each {Number.From([From Date])..Number.From([To Date])}),
Exp1 = Table.ExpandListColumn(AC1, "ListOfDates"),
AC2 = Table.AddColumn(Exp1, "Mon_Fri", each Date.DayOfWeekName([ListOfDates])),
FR1 = Table.SelectRows(AC2, each ([Mon_Fri] <> "Saturday" and [Mon_Fri] <> "Sunday")),
RC1 = Table.RemoveColumns(FR1,{"From Date", "To Date", "Mon_Fri"}),
CT2 = Table.TransformColumnTypes(RC1,{{"ListOfDates", type date}}),
AC3 = Table.AddColumn(CT2, "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([ListOfDates]))),
RC2 = Table.RemoveColumns(AC3,{"ListOfDates"}),
GR = Table.Group(RC2, {"Name", "Quarter"}, {{"SumRate", each List.Sum([Rate]), type nullable number}}),
PC = Table.Pivot(GR, List.Distinct(GR[Quarter]), "Quarter", "SumRate", List.Sum)
in
PC
Solving the challenge of Calculate Quarterly Working Payment with Excel
Excel solution 1 for Calculate Quarterly Working Payment, proposed by Bo Rydobon 🇹🇭:
=LET(a,A2:A7,b,B2:B7,c,C2:C7,d,IF(c,c,9^9),u,UNIQUE(a),p,--({1,4,7,10}&-2022),q,EDATE(p,3),r,NETWORKDAYS(IF(p>b,p,b),IF(q>d,d,q)),
HSTACK(VSTACK(A1,u),VSTACK("Q"&{1,2,3,4},MMULT(N(u=TOROW(a)),r*(r>0)*D2:D7))))Excel solution 2 for Calculate Quarterly Working Payment, proposed by محمد حلمي:
=REDUCE(HSTACK(A1,"Q"&{1,2,3,4}),UNIQUE(A2:A7),LAMBDA(c,d,VSTACK(c,
HSTACK(d,BYCOL(FILTER(DROP(
REDUCE(0,B2:B7,LAMBDA(q,w,LET(u,MIN(OFFSET(w,,1),
DATE(YEAR(w),12,31)),e,SEQUENCE(u-w+1,,w),
x,FILTER(e,(e>=w)*(e<=u)*NETWORKDAYS(e,e)),VSTACK(q,BYCOL(
SEQUENCE(,4),LAMBDA(a,IFERROR(SUM(FILTER(x,
a=-INT(-MONTH(x)/3))^0*OFFSET(w,,2)),0))))))),1),A2:A7=d),
LAMBDA(x,SUM(x)))))))Excel solution 3 for Calculate Quarterly Working Payment, proposed by محمد حلمي:
=LET(
y,DROP(REDUCE(0,B2:B7,LAMBDA(a,d,LET(
e,OFFSET(d,,1),
i,SEQUENCE(IF(e=0,DATE(YEAR(B2),12,31),e)-d+1,,d),
r,-INT(-MONTH(i)/3),
v,UNIQUE(r),m,OFFSET(d,,-1),
IFNA(VSTACK(a,HSTACK(m,v,MAP(v,LAMBDA(x,LET(
w,FILTER(i,r=x),
NETWORKDAYS(@w,MAX(w))*OFFSET(d,,2)))))),m)))),1),
n,TAKE(y,,1),
REDUCE(HSTACK(A1,"Q"&{1,2,3,4}),UNIQUE(n),
LAMBDA(a,d,LET(
e,FILTER(y,n=d),x,INDEX(e,,2),
IFNA(VSTACK(a,
HSTACK(d,TOROW(MAP(UNIQUE(x),
LAMBDA(a,SUM(DROP(e,,2)*(x=a))))))),"")))))
@ : select first value in array
@ = take(array,1,1) = index(array,1,1)
@w = take(w,1)Excel solution 4 for Calculate Quarterly Working Payment, proposed by Oscar Mendez Roca Farell:
=LET(_n,A2:A7, fd, B2:B7, td, C2:C7,_i, EDATE(B2,{0,3,6,9}),_f,EOMONTH(_i,2),_m,IFNA(IFS((_i<=td)*(_f>=fd), NETWORKDAYS(IF(fd>_i,fd,_i), IF(td<_f,td,_f))),)*D2:D7,_u,UNIQUE(_n), REDUCE(HSTACK("Name","Q"&{1,2,3,4}),_u,LAMBDA(i, x, VSTACK(i, HSTACK(x, BYCOL(FILTER(_m,_n=x), LAMBDA(c, SUM(c))))))))Excel solution 5 for Calculate Quarterly Working Payment, proposed by Sunny Baggu:
=LET(
_names, UNIQUE(A2:A7),
LET(
_rs, REDUCE(
"",
_names,
LAMBDA(x, y,
VSTACK(
x,
LET(
_m, SEQUENCE(12),
_q, ROUNDUP(_m / 3, 0),
_s, DATE(2022, _m, 1),
_e, EOMONTH(_s, 0),
_ndays, MAP(_s, _e, LAMBDA(a, b, NETWORKDAYS(a, b))),
_r, XLOOKUP(_m, MONTH(FILTER(B2:B7, A2:A7 = y)), FILTER(D2:D7, A2:A7 = y), , -1) * _ndays *
N(SEQUENCE(12) <= MAX(MONTH(FILTER(IF(C2:C7, C2:C7, EOMONTH(DATE(2022, 12, 1), 0)), A2:A7 = y)))),
_qsum, TOROW(MAP(SEQUENCE(4), LAMBDA(q, SUM(FILTER(_r, _q = q))))),
_qsum
)
)
)
),
HSTACK(_names, DROP(_rs, 1, ))
)
)Excel solution 6 for Calculate Quarterly Working Payment, proposed by LEONARD OCHEA 🇷🇴:
=>A2:D7
=LET(t,A2:D7,w,2022,y,IF(t="",DATE(w,12,31),t),c,LAMBDA(x,INDEX(y,,x)),u,UNIQUE(c(1)),d,NETWORKDAYS.INTL(DATE(w,1,1),DATE(w,12,31)),z,WORKDAY.INTL(DATE(w,1,1),SEQUENCE(d),1), r,LAMBDA(q,MAP(z,LAMBDA(b,SUM((c(1)=q)*(b>=c(2))*(b<=c(3))*c(4))))),s,SEQUENCE(,4,,3),i,DATE(w,s,1),f,DATE(w,s+3,1),HSTACK(VSTACK("Name",u),REDUCE("Q"&SEQUENCE(,4),u,LAMBDA(a,b, VSTACK(a,BYCOL((z>=i)*(zSolving the challenge of Calculate Quarterly Working Payment with Python in Excel
Python in Excel solution 1 for Calculate Quarterly Working Payment, proposed by Alejandro Campos:
from datetime import datetime, timedelta
import calendar
df = xl("A1:D7", headers=True)
df[['From Date', 'To Date']] = df[['From Date', 'To Date']].apply(
lambda col: pd.to_datetime(col.fillna('31/12/2022'), format='%d/%m/%Y')
)
df['Rate'] = df['Rate'].astype(int)
quarters = {f'Q{i}': (datetime(2022, 3*i-2, 1), datetime(2022, 3*i, calendar.monthrange(2022, 3*i)[1])) for i in range(1, 5)}
result = pd.DataFrame([
{
'Name': name,
**{q: sum(np.busday_count(
max(r['From Date'], s).date(),
(min(r['To Date'], e) + timedelta(days=1)).date()) * r['Rate']
for _, r in group.iterrows() if max(r['From Date'], s) <= min(r['To Date'], e))
for q, (s, e) in quarters.items()}
}
for name, group in df.groupby('Name', sort=False)
])
result
&&&