Pivot and MOM

[nadian]

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!

[Sean]

@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

[nadian]

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_Zhang · Accepted solution]

@nadian

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.

[nadian]

Thanks, looks great!

[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.