Optimize SUMX and FIlter measure

[av9]

Hi there, I am trying to find a way to potentially write this dax measure better and improve its speed. I currently get Customer Sales data for each country at different times of the month. So I need to show the latest sales value per customer based on the last date each counties data was refreshed. In order to do this I created 4 measures the main one being: 

Latest Sales Amount =
SUMX (
    VALUES ( Country[Country] ),
    IF (
        [Max Sales Date] = [Last Country Refresh Date],
        [Sum Sales],
        BLANK ()
    )
)

The dependant measures are:

Max Sales Date = CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS ( Customer[Name] ) )

Sum Sales =
CALCULATE (
    SUMX ( VALUES ( Country[Country] ), SUM ( Sales[Amount] ) ),
    FILTER ( Sales, ( MAX ( Sales[Date] ) = [Max Sales Date] ) )
)

Last Country Refresh Date = CALCULATE ( LASTDATE ( ( Sales[Date] ) ) )

Was hoping to see if anyone thinks these could be written better? or have I got a good solution.

[v-yiruan-msft · Accepted solution]

Hi @av9 ,

You can create a measure as below:

01_Measure = 
VAR _tab =
    SUMMARIZE (
        'Sales',
        'Sales'[Customer],
        'Sales'[Country],
        "latest date", MAX ( 'Sales'[Date] ),
        "sAmount",
            CALCULATE (
                SUM ( 'Sales'[Amount] ),
                FILTER ( 'Sales', 'Sales'[Date] = MAX ( 'Sales'[Date] ) )
            )
    )
RETURN
    SUMX ( _tab, [sAmount] )

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[amitchandak]

@av9 , Refer to last sales measure in the file attached after signature

Last Sales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Date] = CALCULATE(max(Sales[Date]), ALLEXCEPT(Sales,Sales[Customer],Sales[Country]))))

[av9]

Attached is the file https://drive.google.com/file/d/1QZ9ZBXsZykX5X5g31Rk63hC4r2OWL0HR/view?usp=sharing

I should also point out in the file I created another measure 'Total Sales' to show zeros instead of blanks.

[amitchandak]

@av9 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try a measure like one of the two

sumx(values(Country[Country]), lastnonblankvalue(Sales[Date] , SUM ( Sales[Amount] )))

or

calculate(sumx(values(Country[Country]), lastnonblankvalue(Sales[Date] , SUM ( Sales[Amount] ))), allexpcept(Country[Country]))

[mahoneypat]

Please try this expression instead.  This assumes that your [Sum Sales] measure is working as intended.  Your SUM(Sales[Amount]) should be wrapped in a CALCULATE(), and the [Max Sales Date] should likely be calculated up front in a variable and not inside the FILTER.  Does that measure return the expected results when used in a table visual with Country?  Is it performant?

Latest Sales Amount =
VAR summary =
    ADDCOLUMNS (
        VALUES ( Country[Country] ),
        "maxdate", [Max Sales Date],
        "lcrefresh", [Last Country Refresh Date],
        "sumsales", [Sum Sales]
    )
RETURN
    SUMX (
        FILTER (
            summary,
            [maxdate] <> [lcrefresh]
        ),
        [sumsales]
    )

Regards,

Pat