Distinct Count of User based on conditions

[Milan14]

Hello everyone,

I would like to have a distinct count of USERID as at end month from date slicer, selected YEAR = 2021 and MONTH = February, include all the users who fulfil these two conditions.

Condition 1: User who registered before the end of February 2021 under ACCOUNT = AAA.
Condition 2: User who performed a transaction with ACCOUNT = BBB before the end of February 2021.

User who fulfils either one condition (condition 1 / condition 2) also will be counted. User will count as 1 user instead of 2 users if the user fulfils both conditions.

Sample table:

Expected outcome in table:

Expected outcome in card:

Total user

3

Thank you

[Anonymous]

Hi,

You can create this column:

No. of User =
VAR end_of_february =
    ENDOFMONTH ( DATE ( 2021, 2, 1 ) )
RETURN
    IF (
        OR (
            AND ( [ACCOUNT] = "AAA", [REGISTERED DATE] <= end_of_february ),
            AND ( [ACCOUNT] = "BBB", [TRANSACTION DATE] <= end_of_february )
        ),
        1,
        0
    )

It will give you the outcome you expect. Summing this column you can get total users.

Regards,

Marc

[Milan14]

Hi @Anonymous ,

Thanks for your help. I have tried out with the formula you suggested but it does not fully achieve my requirement. I have created a measure instead of a column with the formula you suggested and it's work !

This is the measure I created:

No. of user = 

VAR LastSelectedDate = CALCULATE(MAX('Calander'[Date]))

RETURN

CALCULATE(DISTINCTCOUNT('Table'[USERID]), IF(OR('Table'[Transaction Date] < LastSelectedDate && 'Table'[ACCOUNT] = "BBB", 'Table'[Regsitered Date] < LastSelectedDate && 'Table'[ACCOUNT] = "AAA"), 1, 0))

Milan