Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
@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]))))
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.
@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]))
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
92 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |