How Do You Count Rows of YYYY-MM and Present As A Bar Chart In YYYY-MM Time Series Order

[TJM_UK]

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

[TJM_UK]

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]

@TJM_UK 

No worries.  Yes, dragging a text field into values will auto-count them.

Cheers

Phil

[PhilipTreacy · Accepted solution]

Hi @TJM_UK 

Download updated PBIX file

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

[PhilipTreacy]

Hi @TJM_UK 

Can you please supply your PBIX file so I can check it.

Regards

Phil

[TJM_UK]

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.

Test pbix file. 

[TJM_UK]

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]

Hi @TJM_UK 

Download this sample PBIX with the visual below

Like this

Regards

Phil