Forum Discussion
Pivot and MOM
Hi,
I'm having the following table:
Date Ammount Type
January 100 AAA
January 300 BBB
February 400 BBB
February 500 CCC
March 100 AAA
March 800 CCC
April 200 BBB
April 500 CCC
April 500 AAA
Trying to pivot and add MOM (MonthOverMonth) for the last month column:
January February March April MOM
AAA 100 100 500 400
BBB 300 400 200
CCC 500 800 500 -300
So, I fugured out how to pivot, how to create a measure for the MOM. But didn't find any way to combine between those two and present only the last month MOM.
Any ideas?
Thanks!
For a transformation as below.
You could follow the power query sample in attachement, the code seems over the max length and gets truncated, so I'm attaching it in a zip file.
If you have any question, feel free to let me know.
5 Replies
SeanCommunity Champion
nadian You will need a Calendar Table for these to work!
If you you don't have one visit Matt's link
http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
If you do have a Calendar Table use these
Sean, thanks foor your replay.
I actually already did all you mentioned, now I'm trying to have the MOM column only for the last month.
Eric_ZhangMicrosoft Employee
For a transformation as below.
You could follow the power query sample in attachement, the code seems over the max length and gets truncated, so I'm attaching it in a zip file.
If you have any question, feel free to let me know.
- MattAllington
In Excel it is easy - use Fields, Items and Sets. In Power Bi, the UI is not as developed. I suspect you may have to write measures for each month and MOM and add to columns on a Matrix. This not ideal but I am. It aware of another way. Maybe someone else knows different.
