Summarizing Data by Month and Year

[MAkiva1]

Hi, I'm new to Power Bi.  I'd like to summarize my daily intake of new clients in a table or matrix and display the data by month and year.  I have one column, "RetDat" that displays the intake date in the following format: mm/dd/yyyy, time.  How can I sum up the data by month and then by year?

For instance, what I'd like to see is:

2016

Jan: 50

Feb: 45

Mar: 53

.....

2017

Jan: 55

....

[Greg_Deckler]

When you import your data, Power BI should recognize the date time field and create an automatic date hierarchy for you. You then just need to drop that hierarchy into a matrix or line chart or column chart along with your value that you want to display.

[MAkiva1]

I tried this and I received a list of all dates from the database -- it wasn't organized by year or month.  I'm using DirectQuery, not Import.  

[v-shex-msft · Accepted solution]

Hi @MAkiva1,

As smoupre said, you can simply use matrix to achieve your requirement.

Steps:
1. Switch date to hierarchy mode, keep year and month.
2. Drag date to value field and change the summary mode to count.

Regards,

Xiaoxin Sheng