Unique monthly Login

[Emmy66]

Hi,

Could someone please assist me with this.

Below is my sample data.

ID	TimeStamp
add7	27/01/2020 16:10
add7	28/01/2020 16:10
add7	25/03/2020 15:59
add7	09/10/2018 12:20
add7	27/02/2020 16:10
add7	29/02/2020 16:10
add7	15/03/2020 15:59
add7	16/03/2020 15:59
add7	17/03/2020 15:59
add7	18/03/2020 15:59
6519	15/01/2020 17:00
6519	16/01/2020 17:00
6519	17/01/2020 17:00
6519	18/01/2020 17:00
6519	24/06/2020 10:46
2ac6	24/01/2019 10:38
2ac6	25/01/2019 10:38
2ac6	26/01/2019 10:38
2ac6	27/01/2019 10:38
2ac6	28/01/2019 10:38
2ac6	28/02/2020 15:29
2ac6	26/05/2020 08:39
2ac6	23/06/2020 08:30
2ac6	31/07/2018 17:01
2ac6	05/08/2020 16:47
2ac6	10/09/2020 15:35
2ac6	31/10/2018 14:22
2ac6	22/11/2018 16:11
2ac6	20/12/2018 10:31

The proposed output is to report unique users each month rather than the number of times the user logged on. In the sample data above, add7 logged on twice in January, I want to capture it as 1 unique user for that month, also in February to be captured once and other months. Any help on this will be appreciated.

[amitchandak · Accepted solution]

@Emmy66 , You need to have month year column in table or in date table joined to this

Columns
Date = [TimeStamp].date
month Year = format([TimeStamp],"YYYYMM")

Then try measure 

sumx(values(Table[month Year]), calculate(distinctcount(Table[ID])))

or

sumx(values(Date[month Year]), calculate(distinctcount(Table[ID])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Emmy66]

Hi,

One thing I forgot to ask is if I want to pass the measure thru a slicer such that the user can select start & end dates from the slicer e.g. 01/11/2019 to 31/10/2020, any idea how I can go about it please.

[Emmy66]

I managed to figure it out using variables that I created to call the date. Thanks.

[Emmy66]

Hi,

Thank you so much for the solution you provided. It worked for me. Very much appreciated.

[mahoneypat]

Just create a simple measure like this and use it in a visual with a MONTH( ) column created from your Date column.  Or even, make a Date table for your model and relate it on Date.

Unique Users = DISTINCTCOUNT(Table[ID])

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Regards,

Pat

[Emmy66]

Hi, Thanks alot for taking the time to respond to my question. It produced the required result but the total looked weired when compared to @amitchandak solution as pasted below which gives the correct total.