Calculate Running Total for each dealer. The running total is calculated within a bracket of 3 months only. After 3 months bracket, running total will reset. For Dealer – 1-Dec-2021 and 20-Jan-2022 are within 3 months bracket. But next date 25-Mar-2022 is not within 3 months of 1-Dec-2021, hence Running Total resets here. Once running total resets, next bracket of 3 months start. Similarly, 2-Aug-22 is beyond 3 months from 25-Mar-22, hence running total will reset here also. Remember 3 months is not equal to 90 days.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 45
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Unique Records Based on Rules with Power Query
Power Query solution 1 for Generate Unique Records Based on Rules, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
RS = Table.RemoveColumns(
Table.FromRows(
List.RemoveFirstN(
List.Accumulate(
Table.ToRows(Source),
{{"", 0, 0, 0, 0}},
(s, l) =>
let
De = l{0} = List.Last(s){0},
LD = Date.From(List.Last(s){4}),
PD = Date.From(l{1}),
CD = if De and PD <= Date.AddMonths(LD, 3) then LD else PD,
Run = l{2} + (if De and CD = List.Last(s){4} then List.Last(s){3} else 0)
in
s & {l & {Run, CD}}
)
),
Table.ColumnNames(Source) & {"Running Total", "D"}
),
"D"
)
in
RSPower Query solution 2 for Generate Unique Records Based on Rules, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.TransformColumnTypes(A, {{"Date", type date}}),
C = Table.ToColumns(B),
D = List.Generate(
() => [a = 0, b = C{0}{0}, c = C{1}{0}, d = C{2}{0}],
each [a] < List.Count(C{0}),
each [
a = [a] + 1,
b = C{0}{a},
c = if (b = [b] and C{1}{a} < Date.AddMonths([c], 3)) then [c] else C{1}{a},
d = if c = [c] then [d] + C{2}{a} else C{2}{a}
],
each [d]
),
E = Table.FromColumns(C & {D}, Table.ColumnNames(A) & {"Running Total"})
in
EPower Query solution 3 for Generate Unique Records Based on Rules, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source, {{"Date", type date}}),
Sorted = Table.Sort(ChangedType, {{"Dealer", 0}, {"Date", 0}}),
Grouped = Table.Group(
Sorted,
{"Dealer", "Date"},
{
{
"All",
each List.Transform(
List.Zip(
{[Amount], List.Accumulate([Amount], {}, (s, c) => s & {List.Sum({List.Last(s), c})})}
),
each Record.FromList(_, {"Amount", "Running Total"})
)
}
},
GroupKind.Local,
(x, y) => Number.From(((Date.AddMonths(x[Date], 3) <= y[Date])) or (x[Dealer] <> y[Dealer]))
),
ListExpand = Table.ExpandListColumn(Grouped, "All"),
ExpectedOutput = Table.ExpandRecordColumn(
ListExpand,
"All",
{"Amount", "Running Total"},
{"Amount", "Running Total"}
)
in
ExpectedOutputPower Query solution 4 for Generate Unique Records Based on Rules, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name="GRT3month"]}[Content],
GRT = Table.FromColumns(
Table.ToColumns(Source) & {fxRunningTotalDates( List.Buffer(Source[Amount]) , List.Buffer(Source[Dealer]), List.Buffer(Source[Date]) )},
Table.ColumnNames(Source) & {"Running Total"}
)
in
GRT
fxRunningTotalDates:
let
Source = (values as list, grouping as list, dates as list) as list =>
let
GRTList = List.Generate(
()=> [ GRT = values{0}, date = dates{0}, i = 0 ],
each [i] < List.Count(values),
each try
if grouping{[i]} = grouping{[i] + 1} and [date] >= Date.AddMonths(dates{[i] + 1}, -3)
then [GRT = [GRT] + values{[i] + 1}, date = [date], i = [i] + 1]
else [GRT = values{[i] + 1}, date = dates{[i] + 1}, i = [i] + 1]
otherwise [i = [i] + 1],
each [GRT]
)
in
GRTList
in
Source
Power Query solution 5 for Generate Unique Records Based on Rules, proposed by Owen Price:
Nevertheless, here's my attempt.
https://gist.github.com/ncalm/dba7f56b8055994e7ef3d35696044e19
Power Query solution 6 for Generate Unique Records Based on Rules, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sourt = Table.Sort(Source, {{"Dealer", Order.Ascending}, {"Date", Order.Ascending}}),
Accumulate = List.Accumulate(
Table.ToRecords(Sourt),
[dlr = "", dt = 0, ct = 0, rt = {}],
(state, current) =>
if current[Dealer]
<> state[dlr] or Date.AddMonths(Date.From(state[dt]), 3)
< Date.From(current[Date]) or state[ct] = 3
then
[
dlr = current[Dealer],
dt = Date.From(current[Date]),
ct = 1,
rt = state[rt] & {current[Amount]}
]
else
[
dlr = current[Dealer],
dt = Date.From(state[dt]),
ct = state[ct] + 1,
rt = state[rt] & {List.Last(state[rt]) + current[Amount]}
]
)[rt],
Result = Table.FromColumns(
Table.ToColumns(Sourt) & {Accumulate},
Table.ColumnNames(Sourt) & {"Running Total"}
)
in
ResultSolving the challenge of Generate Unique Records Based on Rules with Excel
Excel solution 1 for Generate Unique Records Based on Rules, proposed by Bo Rydobon 🇹🇭:
=LET(t,
A2:C20,
a,
TAKE(
t,
,
1
),
d,
VSTACK(
0,
a
)=a,
VSTACK(HSTACK(
A1:C1,
"Running Total"
),
HSTACK(t,
DROP(REDUCE(0,
SEQUENCE(
ROWS(
t
)
),
LAMBDA(y,
x,
VSTACK(y,
LET(z,
TAKE(
y,
-1,
1
),
w,
INDEX(
d,
x
),
k,
--INDEX(
t,
x,
2
),
v,
IF((k<=EDATE(
z,
3
))*w,
z,
k),
HSTACK(v,
INDEX(
t,
x,
3
)+w*(v=z)*TAKE(
y,
-1,
-1
)))))),
1,
1))))Excel solution 2 for Generate Unique Records Based on Rules, proposed by Bo Rydobon 🇹🇭:
=LET(a,
A2:A20,
b,
B2:B20,
c,
C2:C20,
d,
VSTACK(
0,
a
)<>a,
DROP(REDUCE(HSTACK(
0,
A1:C1,
"Running Total"
),
SEQUENCE(
ROWS(
a
)
),
LAMBDA(y,
x,
VSTACK(y,
LET(z,
TAKE(
y,
-1,
1
),
w,
INDEX(
d,
x
),
k,
--INDEX(
b,
x
),
v,
IF((k>EDATE(
z,
3
))+w,
k,
z),
u,
INDEX(
c,
x
),
HSTACK(v,
INDEX(
a,
x
),
INDEX(
b,
x
),
u,
u+IF(w+(v<>z),
0,
TAKE(
y,
-1,
-1
))))))),
,
1))Excel solution 3 for Generate Unique Records Based on Rules, proposed by محمد حلمي:
=DROP(REDUCE(0,
B2:B20,
LAMBDA(a,
v,
LET(d,
@TAKE(
a,
-1
),
e,
(--v<=d)*(@+v:A20=OFFSET(
v,
-1,
-1
)),
VSTACK(
a,
HSTACK(
IF(
e,
d,
v+90
),
e*TAKE(
a,
-1,
-1
)+OFFSET(
v,
,
1
)
)
)))),
1,
1)Excel solution 4 for Generate Unique Records Based on Rules, proposed by محمد حلمي:
=REDUCE(HSTACK(
A1:C1,
"Running Total"
),
UNIQUE(
A2:A20
),
LAMBDA(a,
d,
LET(v,
FILTER(
A2:C20,
A2:A20=d
),
r,
SCAN(
INDEX(
v,
1,
2
)+90,
INDEX(
v,
,
2
)+0,
LAMBDA(
a,
d,
IF(
dExcel solution 5 for Generate Unique Records Based on Rules, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
A2:C20,
_dl,
TAKE(
_d,
,
1
),
_dt,
--INDEX(
_d,
0,
2
),
_seq,
SEQUENCE(
ROWS(
_d
)
),
_c1,
VSTACK(
1,
DROP(
_dl,
1
) = DROP(
_dl,
-1
)
),
_e1,
LAMBDA(
a,
b,
IF(
AND(
INDEX(
_c1,
b
),
EDATE(
a,
3
) >= INDEX(
_dt,
b
)
),
a,
INDEX(
_dt,
b
)
)
),
_c2,
SCAN(
TAKE(
_dt,
1
),
_seq,
_e1
),
_e2,
LAMBDA(
a,
b,
IF(
AND(
INDEX(
_c1,
b
),
INDEX(
_c2,
b
) = INDEX(
_c2,
b - 1
)
),
a + INDEX(
_d,
b,
3
),
INDEX(
_d,
b,
3
)
)
),
_c3,
SCAN(
TAKE(
_d,
1,
-1
),
_seq,
_e2
),
_r,
HSTACK(
_d,
_c3
),
_r
)Excel solution 6 for Generate Unique Records Based on Rules, proposed by Alexis Olson:
=IF(
A2<>A1,
B2,
IF(
B2Excel solution 7 for Generate Unique Records Based on Rules, proposed by Rajesh Sinha:
=T2+90
V3: =IF(V2""),$T$2:$T$10),0),V2+90,"")
🔊 Column W has Closest dates to Source Dates.
W2: =INDEX(T2:T10, MATCH(MIN(ABS(T2:T10-V2)), ABS(T2:T10-V2), 0))
🔊 Column X has Running SUM Reset point.
X2: =IFERROR(IF(INDEX($W$2:$W$5, MATCH(T2, $W$2:$W$5, 0)),"Reset",""),"")
🔊 Finally Running Sum in Column Y.
Y2: =IF(X2="Reset",0,SUM(Y1)+U2)
Solving the challenge of Generate Unique Records Based on Rules with SQL
SQL solution 1 for Generate Unique Records Based on Rules, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
SELECT
D.DEALER
,CONVERT(VARCHAR, D.DATE, 120) AS DATE
,D.AMOUNT
,CAST(DATEADD(MONTH, 3, D.DATE) AS DATE) AS END_OF_Q_DATE
,FIRST_VALUE(D.DATE) OVER (PARTITION BY D.DEALER ORDER BY D.DATE) AS FIRST_DATE
FROM DATA D
),
CALC
AS
(
SELECT
D.DEALER, D.DATE, D.END_OF_Q_DATE
FROM DATA_PREP D
WHERE
D.DATE = D.FIRST_DATE
UNION ALL
SELECT
F.DEALER, F.DATE, F.END_OF_Q_DATE
FROM
(
SELECT
T.DEALER, T.DATE, T.END_OF_Q_DATE
,ROW_NUMBER() OVER (PARTITION BY C.DEALER ORDER BY T.DATE) AS ORDERING
FROM CALC C
JOIN DATA_PREP T ON C.DEALER = T.DEALER
AND T.DATE > C.END_OF_Q_DATE
) F
WHERE
1 = 1
ANDF.ORDERING = 1
)
SELECT
F.DEALER
, F.DATE
,F.AMOUNT
,SUM(F.AMOUNT) OVER (PARTITION BY F.DEALER, F.BRACKET ORDER BY F.DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING_TOTAL
FROM
(
SELECT
DP.DEALER
, DP.DATE
,DP.AMOUNT
,MAX(C.END_OF_Q_DATE) OVER (PARTITION BY DP.DEALER ORDER BY DP.DATE) AS BRACKET
FROM DATA_PREP DP
LEFT JOIN CALC C ON DP.DEALER = C.DEALER
AND DP.DATE = C.DATE
) F
ORDER BY
1, 2
;
&&&