Count of registered users - monthly and accumulated

[Dbertelsen]

Hi everyone,

So, I have a bit of a conundrup here that I can't seem to solve on my own, so I truly hope that somebody can help me figure out a solution.

Basically, I have the data for users (customers) who has registered with a website.

The data contains the following:

- Registration date

- Unique user ID

- User name

Examples:

Registration date	Unique user ID	User name
18/10/2022	sa3h21as3dh	John Smith
13/09/2022	13gasd35g1as	Adam
14/08/2022	asd3g51s	Jon Snow
21/07/2022	dg1a6s3d8	Karen
19/06/2022	hdf684dssg	Peter Parker
11/05/2021	q4sd6ds8g4s	Alex

In my actual data, I have 800+ user registrations like the onces above, which I would like to have show:

- Registerations monthly

- Total accumulated registrations monthly

Now, here comes the most tricky part (if you ask me):

I also need the accumulated total for a given month, to be cross-referenced with the total orders made for the same given month.

Much like the data above, we have the same data for orders, looking somewhat like this:

Order date	Unique user ID	User name
19/11/2022	sa3h21as3dh	John Smith
19/10/2022	sa3h21as3dh	John Smith
16/08/2022	asd3g51s	Jon Snow
25/07/2022	dg1a6s3d8	Karen
21/06/2022	hdf684dssg	Peter Parker
17/05/2021	q4sd6ds8g4s	Alex

As you can see, a user can be "active" every month, by coming back and making a new purchase/order.

In this example above, you can see that John Smith has made an order in both Oct. and Nov. -> thus, John Smith has been active in both Oct. and Nov.

So, my ultimate goal here is to get to the following insights:

- Total orders per month (all distinct orders) -  ☑️ (done)

- Total users active (made an order) pr month - ☑️ (done)

- Total users registered (accumulated) up until selected month - how? 🤔

[Dbertelsen]

I have an excel file with the data.

Unfortunately I dont think its possible to share/attach excel files to posts here on the forum, right?

The file contains 2 sheets

• Orders
  • OrderDate
  • OrderID
  • OrderAmount (in EUR)
  • UserID
    
    
• Users
  • UserID
  • RegisteredDate

Ultimately, the data basically shows the orders and which user placed it.

It also shows when users registered their profile.

What I need to get to, is to have the following data

1. Rolling total distinct users (Accumulated registered users (monthly))
2. Total distinct users who had an order in month X (monthly)
3. Total distinct users who had an order in month X vs. total accumulated users (so for instance, if I want to see the numbers for September, then it will show me the distinct count of users who had an order in September vs. the total accumulated registered users since up until and including September).
4. How many distinct users that had an order as a percentage of the total accumulated registered users (monthly).

Ideally this is the graph that I want to be able to produce in PowerBI 😂

I can easily do it in Excel, but I have zero luck doing it in PowerBI.

[FreemanZ]

Try to create a measure with the code below:

 

UserAccum := 

VAR CurrentDate = MAX (UserTable[RegistrationDate])

COUNTROWS(

    FILTER(

        ALL(UserTable[ID]),

        UserTable[RegistrationDate]>= CurrentDate

)

[Dbertelsen]

Hmm.. for some reason it's not working for me?

UserAccum =

VAR CurrentDate = MAX(PowerBI_Practitioners[RegisteredDate]

COUNTROWS(

    FILTER(

        ALL(PowerBI_Practitioners[PractitionerId],

        'PowerBI_Practitioners'[RegisteredDate]>= CurrentDate

)


I get the following error message:
The syntax for 'COUNTROWS' is incorrect. (DAX(VAR CurrentDate = MAX(PowerBI_Practitioners[RegisteredDate]COUNTROWS( FILTER( ALL(PowerBI_Practitioners[PractitionerId], 'PowerBI_Practitioners'[RegisteredDate]>= CurrentDate ))).

[Shaurya]

Hi @Dbertelsen,

You can use:

Count = CALCULATE(DISTINCTCOUNT('Table'[Unique User]),FILTER('Table',MONTH(Order Date)<=MONTH(SELECTEDVALUE('Filter'[Date]))))

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

[Dbertelsen]

For some odd reason, this just returns the value "3" 😂