Calculate filtered columns

[Micke]

Hi

I´m new to Power BI, and have now run into a problem that I hope someone can help me solve.

I would like to compare prices and quantities from different months.

If I simplify this is how the table looks like:

Part  Price  Quantity  Month

123   10      100        January

123    9        110       February

What I would like to calculate is: January price of partnumber 123 - February price of partnumber 123 x Quantity of January. 

In the end I would like to be able to filter different months and compare prices and quantities over the year. 

[Twan]

I'm going to assume that you wanted the result of your equation to be for the month of February and that the correct order of operations for you equation would be (January price of partnumber 123 - February price of partnumber 123) x Quantity of January.  I am also going to assume the name of the Table for your data model is Table.

Then the equation can be worded in these terms.

Equation = ( [Previous Month's Price] - [Month's Price] ) * [Previous Month's Quantity]

To solve for [Previous Month's Price] I added a calculated column name [Previous Month].  This calculated column holds the previous month's value.  This column also assumes that the Month column in your data model is a Date/Time field.  If it is not a Date/Time field then you will need to add another calculated column that convert's it to a Date/Time field and then use that column in the equation below.

Previous Month = DATE(YEAR(Table[Month]), MONTH(Table[Month]) -1, DAY(Table[Month]))

Then solve for the Previous Month's price using the previous month date.

Previous Month's Price = CALCULATE(MAX(Table[Price]), FILTER(ALL(Table), Table[Month] = MAX(Table[Previous Month])))

Previous Month's Quantity uses a similar equation:

Previous Month's Quantity = CALCULATE(MAX(Table[Quantity]), FILTER(ALL(Table), Table[Month] = MAX(Table[Previous Month])))

The final equation would then be:

Equation = (  [Previous Month's Price] - SUM(Table[Price]) ) * [Previous Month's Quantity]

Then if you need to filter the equation for a specific part you can do that with slicers or filters in the report view.  Or you can create measures for specific parts that look like this:

Part Equation = Calculate([Equation], Filter(Table, Table[Part] = 123))

Another way to solve this is to create a seperate date dimension with a grain of month.  Then follow the period comparison patterns and semi-additive patterns described at http://www.daxpatterns.com/time-patterns/#semi-additive-pattern.  Look up the [PM Date] and [PM Sales] examples.

-Anthony

[Micke]

Thanks a lot for quick and helpfull answer.

I will try this solution and I´m sure it will work out fine.

Thanks a lot @Twan!!

[Greg_Deckler · Accepted solution]

@Micke - I took @Twan's formulas and put them into a single column. Had to make a few modifications. My data model was the table described called "Prices" and I added a "Months" table that looked like this:

Month,MonthNum

January,1

February,2

March,3

April,4

May,5

June,6

July,7

August,8

September,9

October,10

November,11

December,12

I related Prices to Months via the Month column in each table, then created the following custom column:

Equation = 
VAR Previous_Month = MONTH(DATE(YEAR(TODAY()), IF([Month]="January",12,RELATED(Months[MonthNum]) -1), 1))
VAR Previous_Month_Price = CALCULATE(MAX(Prices[Price]), FILTER(ALL(Prices), RELATED(Months[MonthNum]) = Previous_Month))
VAR Previous_Month_Quantity = CALCULATE(MAX(Prices[Quantity]), FILTER(ALL(Prices), RELATED(Months[MonthNum]) = Previous_Month))
RETURN ((Previous_Month_Price - Prices[Price]) * Previous_Month_Quantity)

[Micke]

Thanks @Greg_Deckler 

This was exactly what I was looking for !!

//Micke