Forum Discussion
How Do You Count Rows of YYYY-MM and Present As A Bar Chart In YYYY-MM Time Series Order
I started with Power BI Desktop yesterday and even after reading many forum posts and trying to use Measures I cannot seem to achieve what I beleive is fairly simple.
From the example table below I wish to create a bar chart of user sign-ups per month. YearMonth is a calculated field and I want to see a bar chart with 2021-01, 2021-02, 2021-03 etc on the x access and a count of signs ups in that period on the y axis.
I've tried to group by YearMonth and count the entries for that group but simply cannot figure out how to do it. If anyone can help that would be appreciated.
| YearMonth | Name | CreatedDate |
| 2021-02 | Lucy | 01/02/2021 16:01 |
| 2021-02 | Tracy | 01/02/2021 15:58 |
| 2021-02 | Qureshi | 01/02/2021 12:44 |
| 2021-02 | Nargis | 01/02/2021 11:55 |
| 2021-02 | Teena | 01/02/2021 11:37 |
| 2021-02 | Sandeep | 01/02/2021 08:48 |
| 2021-02 | Sandeep | 01/02/2021 08:48 |
| 2021-02 | RK | 01/02/2021 03:35 |
| 2021-01 | Jimmy | 30/01/2021 11:55 |
| 2021-01 | Febina | 30/01/2021 11:10 |
| 2021-01 | Paul | 29/01/2021 16:50 |
| 2021-01 | Thomas | 29/01/2021 15:59 |
Hi TJM_UK
ah ok you're using Direct Query. OK a different approach. Create a table from the data you get from Azure. Click New Table in the Ribbon then use this code
Year_Month = SELECTCOLUMNS('Table', "YearMonth", FORMAT([CreatedDate], "yyyy-mm"), "Name", [Name])Creates this
Then use that table to create the visual
Regards
Phil
7 Replies
Dear Phil,
Thank you so much for your help with this. It has been interesting seeing the differences with DirectQuery and creating a new table in BI based on that data.
It took me a while to figure out that dragging the Name field to the axis magically created the count of name. I was trying to find where the formula for that was stored.
- PhilipTreacy
Super User
- PhilipTreacy
Thank you so much !! The PBIX is really helpful but I must be missing something somewhere because my group doesn't count like yours.
As you can see my YearMonth seems to be treated as individual values rather than a group.
- PhilipTreacy
Yes of course. The backend is an Azure SQL database. Not sure if that is the issue. I can't seem to find how to create the Count of Name variable where right clicking gives this:
Hopefully the link here works. Not sure how accessing the back-end Azure database will work for you.
- PhilipTreacy
Hi TJM_UK
ah ok you're using Direct Query. OK a different approach. Create a table from the data you get from Azure. Click New Table in the Ribbon then use this code
Year_Month = SELECTCOLUMNS('Table', "YearMonth", FORMAT([CreatedDate], "yyyy-mm"), "Name", [Name])Creates this
Then use that table to create the visual
Regards
Phil
