Data from D365 - I need to show all accounts with number of activities per month (column grouping

[PowerBITesting]

D365 - I need to show all accounts with number of activitites per month . 

I need to create column grouping with date range - if date range is 1/1/2020 to 31/1/2020. 

Account Name              1/1/2020  1/2/2020  1/3/2020 .......... 1/12/2020          total

-----                                 ----           --------     --------            -------

Alex                                  10               20            30                      10                  70

[v-rzhou-msft · Accepted solution]

Hi @PowerBITesting

Just like parry2k replied before you can use matrix or use power query editor to achieve your goal. 

I build a table to have a test. 

Matrix: 

Firstly build a calculated column. 

MonthYear = YEAR('Table'[Dates])&"-"&MONTH('Table'[Dates])

Result: 

Then we can build a Matrix to achieve your goal. 

Power Query Editor: 

Firstly select Date column and Add two new columns(Year and Month) 

Then select Year and Month column , right-click and merge two columns as a new one. 

Let's select AccountName and MonthYear column and group by. 

Result: 

Finally, select MonthYear column and pivot by Sum 

Result: 

You can download the pbix file from this link:  Data from D365

Best Regards, 

Rico Zhou 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[amitchandak]

@PowerBITesting , that should possible using count.

Can you share sample data and sample output in table format?

[PowerBITesting]

Show all accounts with number of activities per month. 

#Account ABC has 10 activities in january 2020 and 20 activities in feb 2020 and so on. 

#Date columns are dynamic - based on  activity created on ( Grouped by Month)

[parry2k]

@PowerBITesting what is this image? Is this how you are data is? Why you can provide some explanation on what you are sharing rather us doing guess.

[parry2k]

@PowerBITesting it depends on how your data table looks like, you can use matrix visual, put accounts on row, dates on columns, count of activities on values.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡