Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
@MdJ83 , 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
HI @MdJ83
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] )
)
HI @MdJ83
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] )
)
@MdJ83 , 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
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |