Help getting the distinct counts for a customer by category by only the latest transaction date /cat

[robjw]

I have been trying to do the following and it seems like it should be really simple but I can't get it to work. I have a table of data that is millions of rows long but is essentially this: 

customerID	Category	Date
1	a	25/12/2022
1	a	26/12/2022
1	a	27/12/2022
1	a	28/12/2022
2	a	25/12/2022
2	b	26/12/2022
2	b	27/12/2022
2	a	28/12/2022
3	c	25/12/2022
3	b	26/12/2022
3	a	27/12/2022
3	a	28/12/2022

I am trying to get four types of counts across it, represented in one table like below. I am trying to use measures rather than calculated columns to get there as the count needs to be dynamic across date range (the user can filter the date range to what they want, so the maximum dates etc need to change):

Category	Count	Distinct Customer Count	MaxDate Customer Count	MaxCategory Customer Count
a	8	3	3	1
b	3	2	0	1
c	1	1	0	1

The first two counts are fine, but I need help writing DAX formula for:

MaxDate Customer Count: This should count the customer ONLY ONCE in the category that has the max date in the date range selected. As per my example date set, all customers would appear in category A.

MaxCategory Customer Count: This should count the customer ONLY ONCE in the category that was the Max they reached in the date range selected (in actual date the category is a number, so max will work, but thought it might confuse things here)

All the things I've found and try on internet either only return the latest date for the whole data set, or ignore the requirement to only count the customer once across all categories.

Thank you in advance!

[Barthel]

Hey @robjw,

You can use the code below to calculate the MaxDate Customer Count.

MaxCategory Customer Count =
VAR _max_date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        REMOVEFILTERS ( 'Table' ),
        FILTERS ( 'Table'[Date] )
    )
VAR _result =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[customerID] ), 'Table'[Date] = _max_date )
RETURN
    _result

First you calculate the max date taking into account any user date filters. You then calculate the number of unique customers per category based on this.

I don't quite understand the definition of the second measure yet (MaxCategory Customer Count). Could you explain a bit more?

[robjw]

Hi @Barthel thank you very much for coming back to me.

The issue with the maxdate calculation is I think it is only taking the max date across the table, rather than per customer. I have tried to make this more obvious with the below addition to the sample data - customer 4 doesn't have a transaction on the max date overall, but I still want them counted under category B:

customerID	Category	Date
1	a	25/12/2022
1	a	26/12/2022
1	a	27/12/2022
1	a	28/12/2022
2	a	25/12/2022
2	b	26/12/2022
2	b	27/12/2022
2	a	28/12/2022
3	c	25/12/2022
3	b	26/12/2022
3	a	27/12/2022
3	a	28/12/2022
4	a	25/12/2022
4	b	26/12/2022
4	b	27/12/2022

Should give: 

Category	Count	Distinct Customer Count	MaxDate Customer Count	MaxCategory Customer Count
a	9	4	3	1
b	5	3	1	2
c	1	1	0	1

With the formula you provided, the count is 0/blank in category B, rather than showing 1 - if that makes sense?

Regarding MaxCategory Customer count - over time customers can move between category a,b and c. I want to find the max category they were in during the selected time period, counting them only once in that category.  E.g. if they have one transaction in category C, I count them there as the 'max' category they hit during the timeframe.

Hope that makes sense.

[Barthel · Accepted solution]

@robjw,

Yes I understand what you are saying. To achieve this we need to process the customer id in the measure. I added the customer id as a row context within the measure:

MaxDate Customer Count =
COUNTROWS (
    FILTER (
        DISTINCT ( 'Table'[customerID] ),
        VAR _customer = 'Table'[customerID]
        VAR _max_date =
            CALCULATE (
                MAX ( 'Table'[Date] ),
                REMOVEFILTERS ( 'Table' ),
                FILTERS ( 'Table'[Date] ),
                'Table'[customerID] = _customer
            )
        VAR _category =
            CALCULATETABLE (
                VALUES ( 'Table'[Category] ),
                REMOVEFILTERS ( 'Table' ),
                'Table'[customerID] = _customer,
                'Table'[Date] = _max_date
            )
        VAR _result =
            SELECTEDVALUE ( 'Table'[Category] ) IN _category
        RETURN
            _result
    )
)

Any date selection of a user is still taken into account.

The MaxCategory Customer Count works in a comparative manner, but then based on the maximum category. Here, too, any date selections are taken into account.

MaxCategory Customer Count =
COUNTROWS (
    FILTER (
        DISTINCT ( 'Table'[customerID] ),
        VAR _customer = 'Table'[customerID]
        VAR _max_category =
            CALCULATE (
                MAX ( 'Table'[Category] ),
                REMOVEFILTERS ( 'Table' ),
                FILTERS ( 'Table'[Date] ),
                'Table'[customerID] = _customer
            )
        VAR _result =
            SELECTEDVALUE ( 'Table'[Category] ) = _max_category
        RETURN
            _result
    )
)

[robjw]

@Barthel You are officially a genius! This has worked perfectly and I don't think I would have ever got to that answer myself so thank you very much.

[amitchandak]

@robjw , Based on what I got, see if this can help

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...