How to define date ranges in DISTINCTCOUNT measures

[IanR]

I am trying to create three measures that show mutually exclusive counts of customers. My problem is that the sum of my three measures is greater than the total count of customers. I think the cause is how I am defining my dates to include and exclude ion my three measures. The thee measures are:

Active Customers (customers who have bought in the last two years)
Lapsing Customers (customers who bought between 4 and 2 years ago but have not bought since)
Lapsed (customers whose last purchase was over four years ago).

In each case I am doing a DISTINCTCOUNT of customer ID inside a CALCULATE which applies date restrictions. The tables involved are (simplified names):

Watches Owned (sales table with customer ID and purchase date)
Dates (dates table with relationship between the date field in Dates and the purxchase date files in Watches Owned.

These are my measures. Am I doing anything obviously wrong? I haven’t really tested the Lapsed Customers measure as I was already getting [Active Customers] + [Lapsed Customers] > [Total Customers].

Active Customers (0-24) = CALCULATE(DISTINCTCOUNT(tcrmb_watchesownedSet[tcrmb_Customer.Id]),
					DATESBETWEEN(Dates[Date],
					LASTDATE(Dates[Date]) - 730,
					LASTDATE(Dates[Date])
					))

Lapsing Customers (25-48) = 	
VAR UpperDateLimit = LASTDATE(Dates[Date]) - 730
			RETURN
			CALCULATE(DISTINCTCOUNT(tcrmb_watchesownedSet[tcrmb_Customer.Id]),
			DATESBETWEEN(Dates[Date],
				LASTDATE(Dates[Date]) - 1460,  	// 4 * 365
				LASTDATE(Dates[Date]) - 731),		// (2 * 365) + 1		
`			tcrmb_watchesownedSet[tcrmb_DateofPurchase] < UpperDateLimit		
)

Lapsed Customers (49+) = VAR UpperDateLimit = LASTDATE(Dates[Date]) - 1460
			RETURN
			CALCULATE(DISTINCTCOUNT(tcrmb_watchesownedSet[tcrmb_Customer.Id]),
			FILTER(tcrmb_watchesownedSet,
			tcrmb_watchesownedSet[tcrmb_DateofPurchase] <= UpperDateLimit	
			))

Total Customers = CALCULATE([DistinctCountWOCusts], 
			DATESBETWEEN(Dates[Date],
			FIRSTDATE(ALL(Dates[Date])),
			LASTDATE(Dates[Date])
			))

[v-yulgu-msft]

Hi @IanR,

Please try below measures:

Active Customers (0-24) =
CALCULATE (
    DISTINCTCOUNT ( 'Watches Owned'[custom ID] ),
    DATESBETWEEN (
        Dates[Date],
        DATEADD ( LASTDATE ( Dates[Date] ), -2, YEAR ),
        LASTDATE ( Dates[Date] )
    )
)


Lapsed Customers (49+) =
VAR UpperDateLimit =
    DATEADD ( LASTDATE ( Dates[Date] ), -4, YEAR )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Watches Owned'[custom ID] ),
        FILTER (
            'Watches Owned',
            CALCULATE (
                MAX ( 'Watches Owned'[purchase date] ),
                ALLEXCEPT ( 'Watches Owned', 'Watches Owned'[custom ID] )
            )
                <= UpperDateLimit
        )
    )

Lapsing Customers (25-48) =
VAR UpperDateLimit =
    DATEADD ( LASTDATE ( Dates[Date] ), -2, YEAR )
VAR BottomDateLimit =
    DATEADD ( LASTDATE ( Dates[Date] ), -4, YEAR )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Watches Owned'[custom ID] ),
        FILTER (
            'Watches Owned',
            CALCULATE (
                MAX ( 'Watches Owned'[purchase date] ),
                ALLEXCEPT ( 'Watches Owned', 'Watches Owned'[custom ID] )
            )
                < UpperDateLimit
                && CALCULATE (
                    MAX ( 'Watches Owned'[purchase date] ),
                    ALLEXCEPT ( 'Watches Owned', 'Watches Owned'[custom ID] )
                )
                    >= BottomDateLimit
        )
    )

Total customers =
CALCULATE (
    DISTINCTCOUNT ( 'Watches Owned'[custom ID] ),
    DATESBETWEEN (
        Dates[Date],
        FIRSTDATE ( ALL ( Dates[Date] ) ),
        LASTDATE ( Dates[Date] )
    )
)

Best regards,
Yuliana Gu

[IanR]

Hi Yuliana,

Thank you for your code. Unfortunately I think that your code for lapsing customers (last purchased three or four years ago) double counts customers who also bought in the active customers range (last purchased one or two years ago). I've gone with a count of lapsed customers that relies on an EXCEPT to give me customers in the last purchased three or four years ago set that are not also in the last purchased one or two years ago set.

I had to discover the function SELECTCOLUMNS to extract the column that EXCEPT could work on. That’s two new functions for me in one measure as I’d never encountered EXCEPT before either. Scary! I’ve tried to check the figures using MS Access and my measures are in the right area.

Custs Active = VAR ActiveStartDate = LASTDATE(Dates[Date]) - 730
						VAR ActiveEndDate = LASTDATE(Dates[Date])
				RETURN
				COUNTROWS(
					DISTINCT(SELECTCOLUMNS(
								FILTER(ALL(tcrmb_watchesownedSet),
									tcrmb_watchesownedSet[tcrmb_DateofPurchase] >= ActiveStartDate
									&&
									tcrmb_watchesownedSet[tcrmb_DateofPurchase] <= ActiveEndDate
									),
								"CustID", tcrmb_watchesownedSet[tcrmb_Customer.Id]
					)))

Custs Lapsing = VAR ActiveStartDate = LASTDATE(Dates[Date]) - 730
				VAR ActiveEndDate = LASTDATE(Dates[Date])
				VAR LapsingStartDate = LASTDATE(Dates[Date]) - 1460
				VAR LapsingEndDate = LASTDATE(Dates[Date]) - 731
				RETURN
				COUNTROWS( EXCEPT(
					DISTINCT(SELECTCOLUMNS(
								FILTER(ALL(tcrmb_watchesownedSet),
									tcrmb_watchesownedSet[tcrmb_DateofPurchase] >= LapsingStartDate
									&& tcrmb_watchesownedSet[tcrmb_DateofPurchase] <= LapsingEndDate
									),
								"CustID", tcrmb_watchesownedSet[tcrmb_Customer.Id] )),
					DISTINCT(SELECTCOLUMNS(
								FILTER(ALL(tcrmb_watchesownedSet),
									tcrmb_watchesownedSet[tcrmb_DateofPurchase] >= ActiveStartDate
									&& tcrmb_watchesownedSet[tcrmb_DateofPurchase] <= ActiveEndDate
									),
								"CustID", tcrmb_watchesownedSet[tcrmb_Customer.Id] ))))

Custs Lapsed = [Custs Total] - [Custs Active] - [Custs Lapsing]

Thanks

Ian