Forum Discussion
Calculating Average Unit Price Difference between two dynamic time periods
- 4 years ago
You could build a table of products with sales in both periods and then iterate over that, something like
Average of averages = var productsFirstYear = VALUES( 'Sales'[Product ID]) var productsSecondYear = CALCULATETABLE( VALUES( 'Sales'[Product ID]), REMOVEFILTERS( 'Date'), TREATAS( 'Comparison period'[Date], 'Date'[Date]) ) return AVERAGEX( INTERSECT( productsFirstYear, productsLastYear), [Average price diff] ) - 4 years ago
INTERSECT was the key to unlocking this problem, thank you! Needed to vary the DAX a bit but very similar formula, combines your suggestion with the DAX Patterns Comparing Different Time Periods use of USERELATIONSHIP. I have updated my example file for full reference.
Avg Unit Price Diff (Intersect) = VAR productsRegularYear = VALUES ( 'Sales'[ProductKey] ) VAR productsCompareYear = CALCULATETABLE ( VALUES ( 'Sales'[ProductKey] ), REMOVEFILTERS ( 'Date' ), USERELATIONSHIP ( 'Date'[Date], 'Comparison Date'[Comparison Date] ) ) RETURN AVERAGEX ( INTERSECT ( productsRegularYear, productsCompareYear ), [Avg Unit Price Diff (Wrong)] )In addition I was able to extend this pattern to calculate the total price savings, which again requires calculating the intersection of products sold in both periods and then taking the Avg unit price diff * quantity sold in the regular period.
Total Price Savings (Intersect) = /* This measure calculates the total price savings at the intersection between products in the regular period and comparison period as savings must take into account the products sold in both periods Price savings is defined as difference in unit price * units sold in regular period */ VAR materialsMeasurePeriod = VALUES ( Sales[ProductKey] ) VAR materialsBasePeriod = CALCULATETABLE ( VALUES ( Sales[ProductKey]), REMOVEFILTERS ( 'Date' ), USERELATIONSHIP ( 'Date'[Date], 'Comparison Date'[Comparison Date] ) ) RETURN SUMX ( INTERSECT ( materialsMeasurePeriod, materialsBasePeriod ), [Price Savings WRONG] )
You could build a table of products with sales in both periods and then iterate over that, something like
Average of averages =
var productsFirstYear = VALUES( 'Sales'[Product ID])
var productsSecondYear = CALCULATETABLE( VALUES( 'Sales'[Product ID]), REMOVEFILTERS( 'Date'),
TREATAS( 'Comparison period'[Date], 'Date'[Date]) )
return AVERAGEX( INTERSECT( productsFirstYear, productsLastYear), [Average price diff] )- equerystrian4 years agoAdvocate I
INTERSECT was the key to unlocking this problem, thank you! Needed to vary the DAX a bit but very similar formula, combines your suggestion with the DAX Patterns Comparing Different Time Periods use of USERELATIONSHIP. I have updated my example file for full reference.
Avg Unit Price Diff (Intersect) = VAR productsRegularYear = VALUES ( 'Sales'[ProductKey] ) VAR productsCompareYear = CALCULATETABLE ( VALUES ( 'Sales'[ProductKey] ), REMOVEFILTERS ( 'Date' ), USERELATIONSHIP ( 'Date'[Date], 'Comparison Date'[Comparison Date] ) ) RETURN AVERAGEX ( INTERSECT ( productsRegularYear, productsCompareYear ), [Avg Unit Price Diff (Wrong)] )In addition I was able to extend this pattern to calculate the total price savings, which again requires calculating the intersection of products sold in both periods and then taking the Avg unit price diff * quantity sold in the regular period.
Total Price Savings (Intersect) = /* This measure calculates the total price savings at the intersection between products in the regular period and comparison period as savings must take into account the products sold in both periods Price savings is defined as difference in unit price * units sold in regular period */ VAR materialsMeasurePeriod = VALUES ( Sales[ProductKey] ) VAR materialsBasePeriod = CALCULATETABLE ( VALUES ( Sales[ProductKey]), REMOVEFILTERS ( 'Date' ), USERELATIONSHIP ( 'Date'[Date], 'Comparison Date'[Comparison Date] ) ) RETURN SUMX ( INTERSECT ( materialsMeasurePeriod, materialsBasePeriod ), [Price Savings WRONG] )