Create a new table summarizing the total for the each month

[Anonymous]

Hi 

I want to create a new table that summarises the totals for each prodcut see the two table below input and output

Date	Product	Amount
1/01/2021	A	100
2/01/2021	B	200
3/01/2021	C	100
4/01/2021	A	200
5/01/2021	B	300
5/02/2021	A	200
6/02/2021	B	100
7/02/2021	C	50
8/02/2021	B	100
Month	Product	Date
Jan-21	A	300
Jan-21	B	500
Jan-21	C	100
Feb-21	A	200
Feb-21	B	200
Feb-21	C	50

[amitchandak · Accepted solution]

@Anonymous , You can add a month Year column in Power Query

Date.ToText([Date], "MMM-yyyy")

And then use group by https://docs.microsoft.com/en-us/power-query/group-by

OR

Dax add  a column

= format([Date], "mmm-yyyy")

Then use summarize(Table, Table[Month Year], "Total", SUm(Table[Amount]) )

or use on visual with month year and sum of amount

[tamerj1 · Accepted solution]

HI @Anonymous 
You can try the following code

Products Monthly Summary =
VAR T1 =
    ADDCOLUMNS (
        Products,
        "Month",
            FORMAT ( DATE ( 1, MONTH ( Products[Date] ), 1 ), "MMM" ) & "-"
                & RIGHT ( YEAR ( Products[Date] ), 2 )
    )
RETURN
    SUMMARIZE (
        T1,
        [Month],
        Products[Product],
        "Total Amount", SUM ( Products[Amount] )
    )

[amitchandak · Accepted solution]

@Anonymous , You can add a month Year column in Power Query

Date.ToText([Date], "MMM-yyyy")

And then use group by https://docs.microsoft.com/en-us/power-query/group-by

OR

Dax add  a column

= format([Date], "mmm-yyyy")

Then use summarize(Table, Table[Month Year], "Total", SUm(Table[Amount]) )

or use on visual with month year and sum of amount