cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newpi
Helper V
Helper V

Count new sign ups by month.

Hi,

 

I'm doing a very basic count of new accounts created by date/month. So I have a account creation date column and account id (unique per customer) .

 

All I'm trying to do is create a table in desktop where I'm using date and for couting new customers, I have a DAX formula of distinctcount(account id) . So this should ideally give me count of new accounts created by date right? But I'm unable to get that result. My result is instead showing all dates for that month/year and then again every date it is counting all account numbers. Bascially it is not grouping by date and the result looks like this.

1st Jan1000
2nd Jan1000
3rd Jan1000
4th Jan1000

 

It should ideally look like

1st Jan100
2nd Jan210
3rd Jan300
4th Jan405

 

The numbers above are random. Can't seem to figure out what is wrong here and why it is not grouping by date.

1 ACCEPTED SOLUTION

THanks @v-yingjl @aj1973 @amitchandak . This is solved. It was related to fomatting the date columns in my case to have same data type and format across tables and had to do that in the power query view and data view.

View solution in original post

10 REPLIES 10
v-yingjl
Community Support
Community Support

Hi @newpi ,

Not certain what did your dataset look like. I have created a sample table like this and used distinctcount() function to get the right result:

sample table.pngtable result.png

 

Maybe you can share some sample table for further discussion.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

@v-yingjl My data is exactly in a similar format you shared. I'm also using a Date Table to match my sign up date in the account table with date field in the date table and I'm using that date column from date table here

Attached is my measure metric and the result.Screen Shot 2020-06-24 at 8.29.30 AM.pngScreen Shot 2020-06-24 at 8.31.26 AM.png

v-yingjl
Community Support
Community Support

Hi @newpi ,

Could you please consider sharing a sample .pbix file for further discussion?

 

Best Regards,

Yingjie Li

THanks @v-yingjl @aj1973 @amitchandak . This is solved. It was related to fomatting the date columns in my case to have same data type and format across tables and had to do that in the power query view and data view.

View solution in original post

v-yingjl
Community Support
Community Support

Hi @newpi ,

Glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.


Best Regards,
Yingjie Li

aj1973
Community Champion
Community Champion

Hi @newpi 

 

Would you please share a sample of your model! that way we understand better your issue and help you.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

amitchandak
Super User IV
Super User IV

@newpi , with data you shared it is not clear. Make sure you have date table.

 

Create a rank for the dates in the account.

Create a rank column and filter for 1.

RANKX(
FILTER(
'Table',
'Table'[account ]=EARLIER('Table'[account ])
),
'Table'[Creation Date],,asc,dense
)

 

 

Or try a measure like

calculate(count(Table[Account]), values(Table[Account]),filter(All(Date), Date[Date] =Min(Date[Date])))

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

 

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

aj1973
Community Champion
Community Champion

Hi @newpi 

 

No your DAX formula is counting all the rows in the table regardless of dates. If you want to see the count by day you will need to add a slicer of the dates of your table in the desktop, and then use the slicer to filter the day where you want to see the count of IDs.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

@aj1973 My other measure count worked without the slicer but this one is not. Even after adding the slicer the result is the same.

aj1973
Community Champion
Community Champion

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Good luck

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors