Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
106 | |
87 | |
77 | |
69 |
User | Count |
---|---|
124 | |
112 | |
94 | |
84 | |
75 |