Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
79 | |
68 | |
60 |