Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have usernames in Col A and Date of plan Assign in column B. I need a formula that help me the disting count of useres added till up to each month.
User Name | Assign Date |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
leighann.worrall | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
steven.fraser | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
CHRISTOPHER.PEERS | 01/08/2019 |
leighann.worrall | 01/08/2019 |
rebecca.curran | 01/08/2019 |
steven.fraser | 01/08/2019 |
ANTHONY.CASSIDY | 11/14/2018 |
erica.estevao | 12/03/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
erica.estevao | 12/03/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
ANTHONY.CASSIDY | 11/14/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
erica.estevao | 12/03/2018 |
From the above example i will take Assign date as my X Axis. so it will be Nov 2018, Dec 2018, Jan 2019.
in graph i need to get for Nov 2018 the distint count of usernaes assigned for Nov 2018. Then for Dec 2018 i need to get the distint count of users added from Nov 2018 to Dec 2018. And this should continue for every upcoming month. Means for Jan it should get the disintg count from Nov 2018 to Jan 2019.
Please help me with this.
Solved! Go to Solution.
Here is an example file, hope that this helps. I added a date table and then two Dax functions.
Distinct UserNames = DISTINCTCOUNT(Table2[User Name])
Acc. Distinct UserName = VAR minDate = CALCULATE(MIN(Dates[Date]);ALLSELECTED(Dates)) VAR maxDate = MAX(Dates[Date]) VAR distMonth = SUMMARIZE( FILTER( ALL(Dates); Dates[Date] >= minDate && Dates[Date] <= maxDate ); Dates[Year-Month]; "DistCount";[Distinct UserNames] ) RETURN SUMX( distMonth; [DistCount] )
Regards,
Kristjan76
Here is an example file, hope that this helps. I added a date table and then two Dax functions.
Distinct UserNames = DISTINCTCOUNT(Table2[User Name])
Acc. Distinct UserName = VAR minDate = CALCULATE(MIN(Dates[Date]);ALLSELECTED(Dates)) VAR maxDate = MAX(Dates[Date]) VAR distMonth = SUMMARIZE( FILTER( ALL(Dates); Dates[Date] >= minDate && Dates[Date] <= maxDate ); Dates[Year-Month]; "DistCount";[Distinct UserNames] ) RETURN SUMX( distMonth; [DistCount] )
Regards,
Kristjan76
Hi @Kristjan76 thanks for your help. I got the solutuion by using the below formula.
Great
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |