topic Create a new table summarizing the total for the each month in DAX Commands and Tips

Create a new table summarizing the total for the each month

Anonymous — Fri, 18 Mar 2022 02:02:21 GMT

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

Re: Create a new table summarizing the total for the each month

amitchandak — Fri, 18 Mar 2022 02:45:55 GMT

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

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

Re: Create a new table summarizing the total for the each month

tamerj1 — Fri, 18 Mar 2022 03:12:54 GMT

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] ) )