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.
I have a table with user name column, date column along with many other columns.
I want to get count of non-repetitive distinct users each month.
Table is like this:
Name | Date | ID |
prashant | 1/1/2022 | 23123 |
aman | 1/1/2022 | 345 |
ram | 1/1/2022 | 23465 |
shyam | 2/1/2022 | 3467 |
mary | 2/1/2022 | 34 |
kelly | 2/1/2022 | 34234 |
dawn | 2/1/2022 | 656 |
prashant | 2/1/2022 | 675 |
prashant | 3/1/2022 | 3427 |
ram | 3/1/2022 | 7245 |
dawn | 4/1/2022 | 5626 |
ram | 4/1/2022 | 5245 |
shyam | 5/1/2022 | 562434 |
rita | 5/1/2022 | 6465 |
I want results like this:
Month | Count |
Jan-22 | 3 |
Feb-22 | 4 |
Mar-22 | 0 |
Apr-22 | 0 |
May-22 | 1 |
Appreciate your help. Thank you
Power BI Desktop version: Sep 2021
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Count expected measure: =
VAR _maxdateindata =
CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS () )
VAR _currentendofmonth =
MAX ( 'Calendar'[Endofmonth CC] )
VAR _currentmonthname =
VALUES ( Data[Name] )
VAR _previousmonthsname =
CALCULATETABLE (
VALUES ( Data[Name] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Endofmonth CC] < _currentendofmonth )
)
RETURN
IF (
HASONEVALUE ( 'Calendar'[Month-Year CC] ),
IF (
MIN ( 'Calendar'[Date] ) <= _maxdateindata,
COUNTROWS ( EXCEPT ( _currentmonthname, _previousmonthsname ) ) + 0
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Great!! Thank you, This does solve my problem.
Is there a way I can achieve this with main table only. No calculated tables.
Hi,
If you only want to use one table, and if you do not want to use calendar table, please check the below picture and the attached pbix file.
Count expected measure using only Data Table: =
VAR _maxdateindata =
CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS () )
VAR _currentstartofmonth =
MAX ( Data[Month-Year Sort CC] )
VAR _currentmonthname =
VALUES ( Data[Name] )
VAR _previousmonthsname =
CALCULATETABLE (
VALUES ( Data[Name] ),
FILTER ( ALL ( Data ), Data[Month-Year Sort CC] < _currentstartofmonth )
)
RETURN
IF (
HASONEVALUE ( Data[Month-Year CC] ),
COUNTROWS ( EXCEPT ( _currentmonthname, _previousmonthsname ) ) + 0
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Count expected measure: =
VAR _maxdateindata =
CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS () )
VAR _currentendofmonth =
MAX ( 'Calendar'[Endofmonth CC] )
VAR _currentmonthname =
VALUES ( Data[Name] )
VAR _previousmonthsname =
CALCULATETABLE (
VALUES ( Data[Name] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Endofmonth CC] < _currentendofmonth )
)
RETURN
IF (
HASONEVALUE ( 'Calendar'[Month-Year CC] ),
IF (
MIN ( 'Calendar'[Date] ) <= _maxdateindata,
COUNTROWS ( EXCEPT ( _currentmonthname, _previousmonthsname ) ) + 0
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
thanks. This solves the problem.
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 |
---|---|
96 | |
94 | |
80 | |
71 | |
64 |
User | Count |
---|---|
115 | |
106 | |
96 | |
81 | |
72 |