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