PowerPivot - a potential Excel Sumifs equivalent that can be created in a calculated column?

[aes34894328]

I'm new to Powerpivot, and wondering if it's possible to replicate in a PowerPivot calculated column, the sumifs formula in the excel screenshot below (Column E). I've attempted to use the "Calculate" function in PowerPivot, but haven't solved how to use "Previous Month Sale" cells (Column D) as the key for each row, as shown in the screenshot.

Feedback welcome if there's a possible solution to this, ideally in a calculated column. Welcome alternate approaches in Power Pivot as well. Thank you.

 

[Matt Allington]

This is a huge topic. In short, you should not do it the way you describe. To do it properly leveraging power pivot, you need to
1. Create a star schema for your data The Optimal Shape for Power BI Data
2. Include a calendar table as part of the schema Create calendar Tables in Power BI
3. Write time intelligence based measures to do the previous month’s sales calculation DAX Time Intelligence Explained
4. Use a pivot table to display the output you want.

You haven’t specified how you want to use/display the data, so it’s hard to give you specific display advice; the preparation advice above however is sound. My advice is do some reading to learn the principles and then give it a go.

At a high level, I recommend your data (fact) table be structured as
Item, month, amount

Your calendar table should contain all unique months and join to the month column mentioned above. The rest can be done with measures.