% Change from Earliest Date to Latest Date

[ryan_b_fiting]

Hello Community - 

I am in need of some performance tuning help.  I have measures that are calculating the Avg Price during the earliest selected month, avg price during the latest selected month and the % change between those periods.  The formula is providing the correct values that I need, but the performance on these are very bad.  

Here are my measure:

fx_SalesPrice Avg Earliest Month = 
CALCULATE(
    [fx_Sales Price per 100],
    FILTER(
        'COGS_Master_SalesHist','COGS_Master_SalesHist'[InvoiceMonth] = 
    MINX(
    'COGS_Master_SalesHist',
    'COGS_Master_SalesHist'[InvoiceMonth] )
    )
)

fx_SalesPrice Avg Latest Month = 
CALCULATE(
    [fx_Sales Price per 100],
    FILTER(
        'COGS_Master_SalesHist','COGS_Master_SalesHist'[InvoiceMonth] = 
        MAXX(
    'COGS_Master_SalesHist',
    'COGS_Master_SalesHist'[InvoiceMonth] )
    )
)

fx_SalesPrice pct chg = 
DIVIDE(
    ([fx_SalesPrice Avg Latest Month] - [fx_SalesPrice Avg Earliest Month]), [fx_SalesPrice Avg Earliest Month], 0
)

These measures are producing the correct output, but the table takes 10-12 seconds to refresh anytime I change a slicer on the page. 

Are there any suggestions on how to improve the DAX performance on the above measures?

Thanks in advance Community!

Ryan 

[Jihwan_Kim]

Hi,

I am not sure, but please try the below.

fx_SalesPrice Avg Earliest Month =
VAR _minmonth =
    MINX ( 'COGS_Master_SalesHist', 'COGS_Master_SalesHist'[InvoiceMonth] )
RETURN
    CALCULATE (
        [fx_Sales Price per 100],
        FILTER (
            'COGS_Master_SalesHist',
            'COGS_Master_SalesHist'[InvoiceMonth] = _minmonth
        )
    )

fx_SalesPrice Avg Latest Month =
VAR _maxmonth =
    MAXX ( 'COGS_Master_SalesHist', 'COGS_Master_SalesHist'[InvoiceMonth] )
RETURN
    CALCULATE (
        [fx_Sales Price per 100],
        FILTER (
            'COGS_Master_SalesHist',
            'COGS_Master_SalesHist'[InvoiceMonth] = _maxmonth
        )
    )

[ryan_b_fiting]

Hey @Jihwan_Kim thanks for the response.  I had already tried that, and it gives me near the same performance.