Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |