Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
equerystrian
Advocate I
Advocate I

Calculating Average Unit Price Difference between two dynamic time periods

See my adapted example file at Github GMeronek Variation SQL BI 05 01 Comparing different time periods.pbix 


I need help handling whether it's possible for two measures with different filter context to be used in an "Average of Net Difference" type of measure, especially when comparing different time periods and needing to force calculations at a unit level. 

 

I'm attempting to recreate some purchase price comparison reports that are currently hosted in a dynamic SQL-based tool (IBM Cognos). The basic principal is comparing unit prices invoiced per product between a base period and a measurement period, such as prior calendar year vs current first quarter. There's a great DAX Patterns site example for creating measures that can allow you to compute net sales for the base period vs net sales for the measurement period. There's also a good "Average of Averages" example on these forums that allowed me to get the two measures needed to compare Average Unit Price at the product level no matter whether product ID is actually in the displayed results. 
Where I get stuck: the business calculates a Price Savings based on Average Unit Price Base Period - Average Unit Price Measurement Period. That kind of row-by-row difference average works fine in a dynamic SQL report because everything is RBAR no matter what. However, I need to force a row-by-row context to calculate the average difference ONLY for products with sales in both periods. It's immaterial to look at products not sold in both periods for a given subcategory when comparing "what got more expensive?" I need to force this product level filtering into my "difference of average of averages for two different date periods" calculations without resorting to value level filtering on the total visual. If someone is looking at "Total camcorder sales", they don't want to miss out on total sales for a product just because it didn't sell last year. 

See below for example DAX and screenshots. Highly recommend you download the PBIX linked above for full context. 

Avg Unit Price Diff (Wrong) =
/* GMeronek This measure only works if the display table is value filtered to exclude blank Avg Sales Unit Price and blank Comparison Avg Sales Unit Price.
 What we need is to push that filtering into this calculation but at the product level
    So that the avg unit price difference for each product is calculated only for products with sales in both periods
    We need to be able to compare at the product level and bridge the filter context difference somehow 
    The IS BLANK filtering below falls apart once you start grouping at the subcategory level*/

IF (
    ISBLANK ( [Avg Sales Unit Price] )
        || ISBLANK ( [Comparison Avg Sales Unit Price] ),
    BLANK (),
    [Comparison Avg Sales Unit Price] - [Avg Sales Unit Price]
)

2022-04-20_15-58-01_CorrectWithValueFilters.png2022-04-20_15-55-18 Avg Diff Wrong.png



2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

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] )

View solution in original post

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]
    )

 2022-04-21_11-51-06_IntersectSuccess.png

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

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] )

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]
    )

 2022-04-21_11-51-06_IntersectSuccess.png

Whitewater100
Solution Sage
Solution Sage

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).

 

Whitewater100_0-1650501278194.png

 

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 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors