User Registrations by Time Period

[cronline]

Hi,

I have a User Registrations table which is populated when a user registers all their personal details. Each registration is Timestamped with a Registration Date. I would like to build a chart, let's say a line chart, that shows me Months along the horizontal axis and a count of registrations in that month along the vertical access.

Any help in pointing me in the right direction would be appreciated.

Thanks

Chris

[MattAllington]

You should add a calendar table to your data model. Read my article about that  here 

http://exceleratorbi.com.au/power-pivot-calendar-tables/

[v-huizhn-msft · Accepted solution]

Hi @cronline

I try to reproduce your scenario on my local computer and get the expected result as follows.

First, add a calendar table to your data model as Matt posted. The first screenshot below is the register table, the second is calendar table. Create a relationship between them.

 

Second, create a column in calendar, and a measure using the following formula.

MONTH = MONTH(DateTable[Date])
COUNT = CALCULATE(COUNTROWS(Register),ALLEXCEPT(DateTable,DateTable[MONTH]))

COUNTROWS function counts the number of rows in the specified table. The reason of using the function is that same name people may register at one day. So the each row displays one custom.

Finally, create a line chart. Select Month in Axis, count measure as value level, please review the following result.

Best Regards,
Angelia

[cronline]

Thanks for your thorough response Angelia. I have unfortunately hit a stumbling block while trying to follow the first few steps in creating the Date table. I have created a post about that here: http://community.powerbi.com/t5/Desktop/Error-relating-to-the-DATE-Function-when-creating-a-Date-tab...