Creating category column for customer

Hi Anonymous ,

 

You can create a scalable solution using a calendar table with a dynamic MonthIndex. First, ensure you have a calendar table that spans your dataset and includes a calculated MonthIndex column based on the difference from the first date in your data. Here's how you can define it:

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2024, 10, 1), DATE(2025, 3, 31)),
    "Year Month", FORMAT([Date], "YYYY MMM"),
    "MonthIndex", DATEDIFF(MINX(ALL('YourTable'), 'YourTable'[Date]), [Date], MONTH)
)

After that, make sure your Calendar[Date] column is related to 'YourTable'[Date]. Then, when creating the summarized customer-month table, use MonthIndex from the calendar instead of hardcoded month names. Here's an updated version:

CustomerMonthSummary =
SUMMARIZE(
    'YourTable',
    'YourTable'[Customer ID],
    'YourTable'[Location code],
    Calendar[MonthIndex],
    "HasVolume", SUM('YourTable'[Volume]) > 0
)

With this structure, you can write your customer categorization formula in a scalable way without hardcoding any specific months. Use the MonthIndex to determine whether the customer is "Constant", "New", "Renewed", or "Other":

CustomerCategory = 
VAR CustID = 'YourTable'[Customer ID]
VAR LC = 'YourTable'[Location code]

VAR ActiveMonths =
    SELECTCOLUMNS(
        FILTER(CustomerMonthSummary,
            [Customer ID] = CustID &&
            [Location code] = LC &&
            [HasVolume]
        ),
        "Code", [MonthIndex]
    )

VAR AllMonths =
    SELECTCOLUMNS(
        FILTER(CustomerMonthSummary,
            [Customer ID] = CustID &&
            [Location code] = LC
        ),
        "Code", [MonthIndex]
    )

VAR FirstMonth = MINX(ALL('Calendar'), 'Calendar'[MonthIndex])
VAR LastMonth = MAXX(ALL('Calendar'), 'Calendar'[MonthIndex])

VAR IsConstant = COUNTROWS(ActiveMonths) = COUNTROWS(AllMonths)
VAR IsNew = MINX(ActiveMonths, [Code]) = LastMonth
VAR IsRenewed = 
    COUNTROWS(
        FILTER(ActiveMonths, [Code] IN {FirstMonth, FirstMonth + 1, FirstMonth + 2})
    ) > 0 &&
    COUNTROWS(
        FILTER(ActiveMonths, [Code] IN {FirstMonth + 3, FirstMonth + 4})
    ) = 0 &&
    COUNTROWS(
        FILTER(ActiveMonths, [Code] = LastMonth)
    ) > 0

RETURN
SWITCH(TRUE(),
    IsConstant, "Constant",
    IsNew, "New",
    IsRenewed, "Renewed",
    "Other"
)

This version dynamically adapts to any date range in your dataset, so even if you later add more months, the logic will still work without any manual changes.

 

Best regards,