Forum Discussion

Copper Contributor

Sep 16, 2023

Finding unique values in a cumulative range

Hi, I've been grappling over finding a way how many open and closed instances (I'll call them packages) per month, but I need a cumulative value of unique customers. So from the data table, I wan...

BI & Data Analysis

excel

Formulas and Functions

PeterBartholomew1

Silver Contributor

Sep 16, 2023

ajwhitty

= LET(
    distinctPairing, UNIQUE(Table1[[Customer Number]:[Package]]),
    customer,        TAKE(distinctPairing,,1),
    package,         TAKE(distinctPairing,,-1),
    monthStart,      TRANSPOSE(UNIQUE(1 + EOMONTH(+Table1[Start Date], -1))),
    monthEnd,        EOMONTH(monthStart, 0),
    counts, COUNTIFS(
      Table1[Customer Number], customer,
      Table1[Package],         package,
      Table1[Start Date],      ">="&monthStart,
      Table1[Start Date],      "<="&monthEnd
    ),
    VSTACK(
      HSTACK({"Customer","Package"}, TEXT(monthStart, "mmm yy")),
      HSTACK(distinctPairing,        counts)
    )
  )