monthly average sales table from daily sales table

[naeljb]

Hi Power Bi and DaX gurus,

I have a table with the daily sales of two products (table1).   How can I generate another table (table2) that gives me the monthly average sales for both product in a single table?  I used the “groupby”  DaX function but it generates a table with only one product.  Below is my table and the result table that I would like to get. Any tips or help on the DaX formula?

Table1

Date	Sales_A	Sales_B
5/15/2018	20	10
5/16/2018	30	20
6/15/2018	10	40
6/16/2018	20	20

Table2

Date	Average Sales_A	Average Sales_B
May 2018	50	15
June 2018	15	30

Thanks for your help 

[Greg_Deckler · Accepted solution]

First, you need to add a calculated column to you first table:

Month = MONTH([Date])

Then, 

Table = SUMMARIZE('Table',[Month],"Average Sales_A",AVERAGE([Sales_A]),"Average Sales_B",AVERAGE([Sales_B]))

[Greg_Deckler · Accepted solution]

First, you need to add a calculated column to you first table:

Month = MONTH([Date])

Then, 

Table = SUMMARIZE('Table',[Month],"Average Sales_A",AVERAGE([Sales_A]),"Average Sales_B",AVERAGE([Sales_B]))

[naeljb]

Thank Greg.  It works perfectly.  

[naeljb]

it seems that summarize would not work if I have more than 2 products

[Greg_Deckler · Accepted solution]

Sure it would, you just keep adding columns, you can add as many columns to summarize as you wish.

[naeljb]

Yes, you are right.  it works with many column. Thank you very much.