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] )
Hi:
I have something for your review. You can directly connect both Date Tables to sales directly and just choose a slight variation in the name of the same measure being compared. Also turn off interactions so one slice would only effect one data segment and the other impacting the compaison segment (25% lower unit price).
I used a simple total sales / Qty to get avg unit price, feel free to change it to your liking.
You can see the 25% vairiance in price diff and % VAR.
I hope this is what you are looking for..
https://drive.google.com/file/d/15qccerzgTUxLMVO5z7s6tkc7mb6FWnki/view?usp=sharing
- equerystrian4 years agoAdvocate I
Thank you for this suggestion! Unfortunately the user requirements mean that I need to be able to put both the comparison date and regular date measures together in visuals with other attributes. The users wish to be able to side-by-side per product as they are used to doing with their existing dynamic SQL reports. The example file you've provided doesn't allow for that as the measures are using whatever date is being used to filter the given visual. Great for many use cases, unfortunately not for this one. I expect that the model solution I create will also need to work from an "Analyze in Excel" perspective, another reason that the measures unfortunately need to be more forcibly defined.
Thanks again for your suggestion though!