Forum Discussion

equerystrian's avatar
equerystrian
Advocate I
4 years ago
Solved

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 conte...
  • johnt75's avatar
    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] )
  • equerystrian's avatar
    equerystrian
    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]
        )