Forum Discussion
How to summarize by date range and grouping customer items
We have a table to summarize such that data is arranged by customer within specific dates. For example, 26 th to end of month plus the next 25 days as shown in this figure. How can this be done ...
=PIVOTBY(A2:B31,LET(_daterange,HSTACK(
BYROW(C2:C31,LAMBDA(x,DATE(YEAR(x),MONTH(x)-1,DAY(I1)))),
BYROW(C2:C31,LAMBDA(r,LET(y,EOMONTH(r,0),DATE(YEAR(y),MONTH(y),DAY(J1)))))),
DROP(REDUCE("",C2:C31,LAMBDA(u,v,
VSTACK(u,
XLOOKUP(1,
(v>=INDEX(_daterange,,1))*(v<=INDEX(_daterange,,2)),
_daterange,
HSTACK(
DATE(YEAR(v),MONTH(v),DAY(I1)),
LET(y,EOMONTH(v,1),DATE(YEAR(y),MONTH(y),DAY(J1)))))))),1)),
D2:D31,SUM,
,0)This formula returns the intended result in my Excel online sample sheet.