How to find non repetitive values each month

[pbhagaur]

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

[pbhagaur]

Great!! Thank you, This does solve my problem.

Is there a way I can achieve this with main table only. No calculated tables.

[Jihwan_Kim]

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
    )

[Jihwan_Kim · Accepted 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
        )
    )

[pbhagaur]

thanks. This solves the problem.