Forum Discussion
Group by date into month
From table A
date organization_id merchant_id price number_of_bookings
01/01/18 A 1001 50 3
02/01/18 A 1001 30 2
01/01/18 B 1031 50 3
02/01/18 B 1041 30 2
I would like to aggregate the date into month, so that table A becomes:
date organization_id merchant_id price number_of_bookings
01/18 A 1001 80 5
01/18 B 1031 50 3
01/18 B 1041 30 2
The aggregation I'd like is group by organization_id, by merchant_id, sum price, sum number_of_bookings.
How can I achieve this?
*Please note that I try to aggregate date into month to trim the file size, not just for visualization purposes.
Hi,
I suggest proceed the aggregation steps in Power Query Editor.
The sample pbix file's link is down below.
The link to the sample pbix file
5 Replies
alpb , if you want it for file reduction, Split column in power query or use option text before/after the delimiter
And then group by
Else in DAX column
format([Date], "mm/yyyy") // or YYYYMM
Hi,
I suggest proceed the aggregation steps in Power Query Editor.
The sample pbix file's link is down below.
The link to the sample pbix file
how do I insert month name and year name, please?
Hi,
In Power Query,
please try to click the below red color squares.
managed to replicate the steps, thank you.
Just one question, grouping by month and by year separately, will that cause a problem?
Should I merge month and year first before doing a group by?
