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.
It should ideally look like
The numbers above are random. Can't seem to figure out what is wrong here and why it is not grouping by date.
Solved! Go to Solution.
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.
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.
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.
@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.
'Table'[account ]=EARLIER('Table'[account ])
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
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 :
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.
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.
Please see this post regarding How to Get Your Question Answered Quickly:
Check out the News & Announcements to learn more.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
Mark your calendars and join us for our next Power BI Dev Camp!
Check out our new Discover Your Career Path blog post series and get all the details.