Count new sign ups by month.

[newpi]

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 Jan	1000
2nd Jan	1000
3rd Jan	1000
4th Jan	1000

It should ideally look like

1st Jan	100
2nd Jan	210
3rd Jan	300
4th Jan	405

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

[v-yingjl]

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:

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.

[newpi]

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

[v-yingjl]

Hi @newpi ,

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

Best Regards,

Yingjie Li

[newpi · 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.

[v-yingjl]

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]

Hi @newpi 

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

[amitchandak]

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

[aj1973]

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.

[newpi]

@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]

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