Distinct count of users via Created By column

[KejGdr]

I have this formula

CALCULATE(
    DISTINCTCOUNT('NG - Self Check Request List'[Created By.title]),
    YEAR('NG - Self Check Request List'[Created]) = YEAR(TODAY()),
    MONTH('NG - Self Check Request List'[Created]) = MONTH(TODAY()) - 1
)

/*CALCULATE(
    DISTINCTCOUNT('NG - Self Check Request List'[Created By.title]),
    FILTER(
        ALL('NG - Self Check Request List'),
        MONTH('NG - Self Check Request List'[Created]) = MONTH(TODAY()) - 1 &&
        YEAR('NG - Self Check Request List'[Created]) = YEAR(TODAY())
    )
)*/

it seems like this is an incorrect formula because it is showing 36, where it is only 19 users if checked manually on the sharepoint list. I fear that it is not counting distinctly thats why some counts were incorrect

[Ahmed-Elfeel · Accepted solution]

Hi @KejGdr,

I hope you are doing well today❤️

So here is some approaches you can try and tell me if it works ☺️❤️

First Approache: (Using DATEADD)

Distinct Users Last Month = 
CALCULATE(
    DISTINCTCOUNT('NG - Self Check Request List'[Created By.title]),
    DATEADD(TODAY(), -1, MONTH)
)

Second Approache: (Using Proper date filtering) - This should work perfectly 

Distinct Users Last Month = 
VAR CurrentDate = TODAY()
VAR FirstDayLastMonth = EOMONTH(CurrentDate, -2) + 1
VAR LastDayLastMonth = EOMONTH(CurrentDate, -1)
RETURN
CALCULATE(
    DISTINCTCOUNT('NG - Self Check Request List'[Created By.title]),
    'NG - Self Check Request List'[Created] >= FirstDayLastMonth,
    'NG - Self Check Request List'[Created] <= LastDayLastMonth
)

Third Approache: Using PREVIOUSMONTH (if you have a date table)

Distinct Users Last Month = 
CALCULATE(
    DISTINCTCOUNT('NG - Self Check Request List'[Created By.title]),
    PREVIOUSMONTH('Date'[Date])
)

Bonus Approache: Debugging version to verify your data

// Firstly check if the date filtering is working correctly
Count Last Month Records = 
VAR CurrentDate = TODAY()
VAR FirstDayLastMonth = EOMONTH(CurrentDate, -2) + 1
VAR LastDayLastMonth = EOMONTH(CurrentDate, -1)
RETURN
CALCULATE(
    COUNTROWS('NG - Self Check Request List'),
    'NG - Self Check Request List'[Created] >= FirstDayLastMonth,
    'NG - Self Check Request List'[Created] <= LastDayLastMonth
)

// Then check distinct users
Distinct Users Last Month = 
VAR CurrentDate = TODAY()
VAR FirstDayLastMonth = EOMONTH(CurrentDate, -2) + 1
VAR LastDayLastMonth = EOMONTH(CurrentDate, -1)
RETURN
CALCULATE(
    DISTINCTCOUNT('NG - Self Check Request List'[Created By.title]),
    'NG - Self Check Request List'[Created] >= FirstDayLastMonth,
    'NG - Self Check Request List'[Created] <= LastDayLastMonth
)

Important Tips:

• Dont forget to check for blank values

Distinct Users Last Month (No Blanks) = 
CALCULATE(
    DISTINCTCOUNT('NG - Self Check Request List'[Created By.title]),
    'NG - Self Check Request List'[Created By.title] <> BLANK(),
    DATEADD(TODAY(), -1, MONTH)
)

• Also verify your date range by creating a simple table visual with the date range you are filtering

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[KejGdr]

Hi @Ahmed-Elfeel  , 

I double checked and it seems like 36 was the correct answer all along for last month meaning my formula has also worked which is weird..

[Ahmed-Elfeel]

Glad it worked! Please mark as solution if this resolved your issue 😊

[KejGdr]

Hi @amitchandak  

nothing changes this is like my 1st formula that I have tried but less the ALL() function correct? and interchanged the YEAR() and MONTH()

[amitchandak]

@KejGdr , Try like

CALCULATE(
    DISTINCTCOUNT('NG - Self Check Request List'[Created By.title]),
    filter('NG - Self Check Request List', YEAR('NG - Self Check Request List'[Created]) = YEAR(TODAY()) && 
    MONTH('NG - Self Check Request List'[Created]) = MONTH(TODAY()) - 1
) )