Forum Discussion

ajwhitty's avatar
ajwhitty
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 want the values to be:

Package Type A:

2 unique customers in April, 2 unique customers in May and then for months after, just 1 Unique customer for package type A

 

I've tried using the UNIQUE function, with VSTACK and whilst I can get unique values of the new packages in month and unique values of the closed packages in month, I can't seem to crack a balance per month.

 

I'm sure this really simple but I don't seem to have the imagination to make it work. Please help 🙂

 

 

Customer NumberPackageStart DateEnd Date
123456A15/04/202315/05/2023
123456A15/04/2023Open
123456A01/06/202325/06/2023
456789B01/04/202331/08/2023
456789B01/07/2023Open
456789B01/04/202325/06/2023
654321A01/04/202331/05/2023
654321A01/05/202331/05/2023
BI & Data Analysis
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    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)
    )
  )

Resources