Calling DAX Masters - Price Realization Over Time

[n8ball]

I've been working on this measure for a long time. Trying to find the best performance. Currently it is not good once you start filtering by Months. Lots of nested iterators and high materialization. I'm starting to think I'm going to have to model this differently. 

I recreated it in Contoso so you have a model to work with. I need the logic in place that eliminates any rows that don't have positive sales or quantity in either period and in my production model I also have to remove return rows. I also need to evaluate price realization by channel as there can be different discount levels and strategies in different channels for the same product. Thanks in advance for any help or guidance you can spare. Model Download

Here is the measure:

Price Realization by Channel = 
VAR YTDTable =
    CALCULATETABLE( DATESYTD( 'Calendar'[DateKey] ), 'Calendar'[DatesInThePast] = TRUE )
VAR PYTDTable =
    CALCULATETABLE(
        SAMEPERIODLASTYEAR( YTDTable ),
        'Calendar'[DatesInThePast] = TRUE
    )
VAR MaterialTable =
   CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE( 'Sales', 'Product'[ProductKey], 'Channel'[Channel]),
            "@ValueYTD", CALCULATE( SUM('Sales'[SalesAmount]), YTDTable ),
            "@QtyYTD", CALCULATE( SUM('Sales'[SalesQuantity]), YTDTable ),
            "@ValuePYTD", CALCULATE( SUM('Sales'[SalesAmount]), PYTDTable ),
            "@QtyPYTD", CALCULATE( SUM('Sales'[SalesQuantity]), PYTDTable )
        ),
        'Sales'[ReturnAmount] = 0
   )
VAR MaterialTable_Filtered =
    FILTER(
        MaterialTable,
        [@ValueYTD] > 0
            && [@QtyYTD] > 0
            && [@ValuePYTD] > 0
            && [@QtyPYTD] > 0
    )
RETURN
    SUMX(
        MaterialTable_Filtered,
        ( DIVIDE( [@ValueYTD], [@QtyYTD] ) - DIVIDE( [@ValuePYTD], [@QtyPYTD] ) ) * [@QtyYTD]
    )

Another approach I tried was SUMX over a Filter and Sumerize. Reduced materialization but required more storage engine queries and as a result performed worse.

Price Realization by Channel 2 = 
VAR YTDTable =
    CALCULATETABLE (
        DATESYTD ( 'Calendar'[DateKey] ),
        'Calendar'[DatesInThePast] = TRUE
    )
VAR PYTDTable =
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( YTDTable ),
        'Calendar'[DatesInThePast] = TRUE
    )
RETURN
    CALCULATE (
        SUMX (
            FILTER (
                SUMMARIZE ( 'Sales', 'Product'[ProductKey], 'Channel'[Channel] ),
                CALCULATE ( SUM ( 'Sales'[SalesAmount] ), YTDTable ) > 0
                    && CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), YTDTable ) > 0
                    && CALCULATE ( SUM ( 'Sales'[SalesAmount] ), PYTDTable ) > 0
                    && CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), PYTDTable ) > 0
            ),
            (
                (
                    (
                        CALCULATE ( SUM ( 'Sales'[SalesAmount] ), YTDTable )
                            / CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), YTDTable )
                    )
                )
                    - (
                        (
                            CALCULATE ( SUM ( 'Sales'[SalesAmount] ), PYTDTable )
                                / CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), PYTDTable )
                        )
                    )
            )
                * CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), YTDTable )
        ),
        'Sales'[ReturnAmount] = 0
    )

[puneetvijwani]

@n8ball  Will Suggest you to change the model and try  Pre loading the data as AggregatedSales table by either on date or by month level granularity
For eg below 

AggregatedSales = SUMMARIZE( FILTER('Sales', 'Sales'[ReturnAmount] = 0), 'Product'[ProductKey], 'Channel'[Channel], 'Calendar'[DateKey], "SalesAmount", SUM('Sales'[SalesAmount]), "SalesQuantity", SUM('Sales'[SalesQuantity]) )

and then create DAX 

or you can materlize and filter your table which you are doing as pre calculation step  in cleansing process and loadit as an Aggregate table , It shall reduce lots of complexity 

Foy dynamic time intelligence you can use something as below article by phil seamark

https://dax.tips/2019/10/09/row-based-time-intelligence/

------------------------------------------------------------------------------------------

Linkedn :-https://www.linkedin.com/in/puneet-v-376470135/
Youtube :-https://www.youtube.com/@datatrends101

[n8ball]

Yup. I will have to try to generate a price table. The only issue there is you loose the dynamic nature of the measure. I think we would need to wrap the SUMs you have above in a CALCULATE to perform context transition to get the right result. Also would need do YTD and PYTD calc for each date.