Forum Discussion
Calculate filtered columns
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.
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)
4 Replies
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 - 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)
