cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

Find value from column X corresponding the max value in column y

I have a small problem that I cant solve in efficient way. I want to create a new column "lastDatesales" which has "sum of sales "corresponding to the last purchase dates sales a company has. For example, company "a":s last purchase date was 10.1.2019, so I want to get the corresponding sales from that date to each row the company has.

My current solution is:

lastDatesales = maxx(filter(Sheet1;Sheet1[company]=earlier(Sheet1[company]));if(Sheet1[date]=maxx(filter(Sheet1;Sheet1[company]=earlier(Sheet1[company]));Sheet1[date]);Sheet1[sales];0))

 date sales company lastDatesales (correct answer) 1.1.2019 100 a 200 10.1.2019 200 a 200 1.1.2018 300 b 600 15.1.2018 600 b 600
1 ACCEPTED SOLUTION
Community Support

Hi @user555

Modify az38's solution as below, then it can use to create a column

``````lastDatesales =
var _lastDate = calculate(MAX(Sheet1[date]),ALLEXCEPT(Sheet1,Sheet1[company]))
return
LOOKUPVALUE(Sheet1[sales],Sheet1[company],Sheet1[company],Sheet1[date],_lastDate)``````

Measure may perform quicker than column.

Check the performance under View->Performance Analyzer.

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

3 REPLIES 3
Community Support

Hi @user555

Modify az38's solution as below, then it can use to create a column

``````lastDatesales =
var _lastDate = calculate(MAX(Sheet1[date]),ALLEXCEPT(Sheet1,Sheet1[company]))
return
LOOKUPVALUE(Sheet1[sales],Sheet1[company],Sheet1[company],Sheet1[date],_lastDate)``````

Measure may perform quicker than column.

Check the performance under View->Performance Analyzer.

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

New Member

Community Champion

hi @user555

try a measure

``````lastDatesales =
var _lastDate = calculate(MAX(Sheet1[date]);ALLEXCEPT(Sheet1;Sheet1[company]))
return
LOOKUPVALUE(Sheet1[sales];Sheet1[company];SELECTEDVALUE(Sheet1[company]);Sheet1[date];_lastDate)``````

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution