Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
You should add a calendar table to your data model. Read my article about that here
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
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |