Nov 29 2022 08:50 AM
I am using the following formula to count the amount of unique freight carriers I use each day at work:
=SUM(--LEN(UNIQUE(FILTER(Orders[Carrier],Orders[Shipped]=Dates[@[11/22]],"")))>0))
Orders[Carrier] has the name of the freight carrier for each order, Orders[Shipped] has the date the order shipped, and Dates[@[11/22]] is from a table that is just a list of dates to reference in formulas so that certain ones are easier to copy/paste.
But now, I'm trying to modify the same formula to count the amount of unique carriers used each month:
=SUM(--LEN(UNIQUE(FILTER(Orders[Carrier],AND(Orders[Shipped]>=AT3,Orders[Shipped]<=AT32),"")))>0))
AT3 shows 11/1/2022, and AT32 shows 11/30/2022.
But...this formula returns 0. How do I make the formula so that it counts the amount of unique carriers used between 11/1 and 11/30?
Nov 29 2022 08:54 AM
AND returns only a single TRUE/FALSE value, not an array. Try
=SUM(--LEN(UNIQUE(FILTER(Orders[Carrier],(Orders[Shipped]>=AT3)*(Orders[Shipped]<=AT32),"")))>0))
Nov 29 2022 10:01 AM
Since you are using FILTER, you may have LAMBDA. AND can be made to operate pairwise by placing it with the MAP helper function
= UNIQUE(
FILTER(Orders[Carrier],
MAP(Orders[Shipped],
LAMBDA(shippingDate,
AND(shippingDate>=start, shippingDate<=end)
)
)
)
)
Naming the Lambda functions can also be useful
= UNIQUE(
FILTER(Orders[Carrier],
MAP(Orders[Shipped], Betweenλ(start, end))
)
)
where
Betweenλ
= LAMBDA(start,end,
LAMBDA(shippingDate,
AND(shippingDate>=start,shippingDate<=end
)
)