Creating category column for customer

[Dominikos12345]

Hi all,

Due to my beginner knowledge of DAX,  I would like to ask for help with creating Category collumn for customers in PBI table view. I have tried AI, but after hours of trying, ending up still with incorrect results.

In below dataset (screnshhot sample), where i have data since Oct 24 to Mar 25, I need to create categorization of customers as follow: 2 alternatives:

1. alt

"Constant"- combination. of Customer ID and Location code (LC) where client have some volumes in every month of my dataset.

"New"-combination. of Customer ID and LC where client have some volumes only since March 25.

"Renewed"- combination. of Customer ID and LC where client have some volumes in period Oct-Dec 24, then 0 volumes in Jan-Feb25 and then have some volumes in Mar 25

"Other"- remaining category where the client does not fit to any 3 above.

2.alt

creating just 2 category:"Renewed" and "Other" ("constant" and "new" will fall in to this cat.)

Thank you in advance,

BR,

Dominik

[DataNinja777]

Hi @Dominikos12345 ,

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,

[Dominikos12345]

Hello DataNinja777

Thank you for you response, I have tried also this method, but this one is giving me incorrect results.

When creating table CustomerMonthSummary, shoud not I also create relationship with my main dataset(but i do not know how, as there is only option many to many)?

[techies · Accepted solution]

Hi @Dominikos12345 please try this calculated column

Customer Category =

VAR Has_Oct_Dec =

    CALCULATE(

        SUM(Sheet6[Volume]),

        ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code]),

        Sheet6[year month] IN {"2024-10", "2024-11", "2024-12"}

    ) > 0

VAR Has_Only_Zero_Jan_Feb =

    CALCULATE(

        SUM(Sheet6[Volume]),

        ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code]),

        Sheet6[year month] IN {"2025-01", "2025-02"}

    ) = 0

VAR Has_Mar =

    CALCULATE(

        SUM(Sheet6[Volume]),

        ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code]),

        Sheet6[year month] = "2025-03"

    ) > 0

VAR Has_All_Months =

    CALCULATE(

        COUNTROWS(FILTER(Sheet6, Sheet6[Volume] > 0)),

        ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code])

    ) = DISTINCTCOUNT(Sheet6[year month])

RETURN

SWITCH(

    TRUE(),

    Has_All_Months, "Constant",

    Has_Mar && NOT Has_Oct_Dec && Has_Only_Zero_Jan_Feb, "New",

    Has_Oct_Dec && Has_Only_Zero_Jan_Feb && Has_Mar, "Renewed",

    "Other"

)

[Dominikos12345]

Hello Techies, 

Thank you very much, this one is working:)

BR,

Dominik