topic Re: Distinct count of users via Created By column in DAX Commands and Tips

Distinct count of users via Created By column

KejGdr — Mon, 17 Nov 2025 07:48:11 GMT

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

Re: Distinct count of users via Created By column

amitchandak — Mon, 17 Nov 2025 08:23:20 GMT

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 ) )

Re: Distinct count of users via Created By column

KejGdr — Mon, 17 Nov 2025 13:47:00 GMT

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()

Re: Distinct count of users via Created By column

Ahmed-Elfeel — Mon, 17 Nov 2025 22:19:44 GMT

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.

Re: Distinct count of users via Created By column

KejGdr — Thu, 20 Nov 2025 09:49:49 GMT

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..

Re: Distinct count of users via Created By column

Ahmed-Elfeel — Thu, 20 Nov 2025 22:08:54 GMT

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