Forum Discussion
Helper VUnique monthly Login
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.
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.
6 Replies
- mahoneypat
Microsoft Employee
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
Emmy66Hi, 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.
- amitchandak
Super User
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,
Thank you so much for the solution you provided. It worked for me. Very much appreciated.
- 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.
