Cumulative total not working with distinct count on text

[WOLFIE]

Hi all,

Could you please help with the below? I used this technique many times with integers,which worked fine, but it doesn't work when doing distinct count on text. I need to get cumulative total of customers for 3 different date columns. Sales table data is in the comment below.

Data model:

Cumulative total_customer count (sofa sales) =

VAR Customer_count =

CALCULATE(DISTINCTCOUNT('Sales'[Customer ID])

,USERELATIONSHIP('Calendar'[Date],'Sales'[Sofa sales date]))

VAR MaxDate = MAX ( 'Calendar'[Date] ) -- Saves the last visible date

RETURN

CALCULATE (

Customer_count,

'Calendar'[Date] <= MaxDate, -- Where date is before the last visible date

ALL('Calendar'[Date]) -- Removes any other filters from Date

)

I need to create identical measures for all 3 date columns, therefore using the inactive relationships. 

[WOLFIE · Accepted solution]

I worked it out! 😄 

[WOLFIE]

data:

Customer ID	Sofa sales	TV sales	Chair sales
1	01/01/2021	02/03/2021	06/03/2021
2	30/12/2020	14/03/2021	16/04/2021
3	16/12/2020	01/01/2021	16/05/2021
4	03/02/2021	15/06/2021
5	26/02/2021
6	13/12/2020	18/02/2021
7	16/01/2021	24/02/2021	30/03/2021
8	02/04/2021	28/05/2021
9	03/01/2021	26/01/2021
10	26/04/2021
11	18/02/2021	28/05/2021
12	13/12/2020
13	26/02/2021	28/05/2021	03/06/2021
14	28/05/2021	15/06/2021
15	13/12/2020
16	18/02/2021	14/03/2021	20/05/2021
17	13/12/2020
18	13/12/2020	15/06/2021
19	15/06/2021
20	13/12/2020
21	16/12/2020	14/03/2021
22	15/06/2021
23	13/12/2020