Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
28 | |
22 |