cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Get max value from related table

Hi there.

I have got 2 tables linked. One with items, like:

Item_ID - Description....

The other one is about pricing of items. This is:

Item_ID - Year - Month - Sales_Amount

There's a relationship between both Item_ID fields.

 

In the first one, I created a measure to get last year where a item was sold.

This is:

lastYear := MAX(Sales[Year])

 

Then, a second measure to get the last sales month. This is:

lastMonth := CALCULATE(MAX('Sales'[Month]);FILTER(Sales;Sales[Year]=MAX(Sales[Year])))

 

Finally, a third measure with the amount of sold item. (I wanna know what was the last item price, due I haven't got any date, just year and month as integers) 

This measure is:

Amount:=CALCULATE(
MAX(Sales[Amount]);
FILTER(Sales;[Year]=[lastYear]);
FILTER(Sales;[Month]=[lastMont])
)

 

It doesn't work and I get only list price. Changing FILTER(Sales;[Year] = MAX(Sales[Year])) instead, I get some results but not the last price. 

 

Cheers.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

First of all, your statements are not clear. You say you want the amount on the latest month of the latest year.... and then you say something about list price. Where the heck do you have a list price column in your tables?

 

When using FILTER, you should always remember that if you use a measure in the filtering condition, the measure performs a context transition because each measure is wrapped in CALCULATE implicitly. You should understand how context transition works before you even start using (measures in) FILTERs.

 

Here's probably what you want:

[Latest Sale Year] = MAX( Sales[Year] )

[Latest Sale Month] =
var __latestYear = [Latest Year]
var __latestMonth =
	CALCULATE(
		MAX( Sales[Month] ),
		Sales[Year] = __latestYear
	)
return
	__latestMonth
	
[Latest Amount] =
var __latestMonth = [Latest Month]
var __latestYear = [Latest Year]
var __latestAmount =
	CALCULATE(
		MAX( Sales[Amount] ),
		Sales[Year] = __latestYear,
		Sales[Month] = __latestMonth
	)
return
	__latestAmount

If you have selected many items, you'll get the maximum amount for any of the items in the set that have the same latest year and latest month. If you want this measure(s) to work only when one item is visible, then you must guard your measures with HASONEVALUE or HASONEFILTER.

 

Best

Darek

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

By the way... Your current design is not proper. You should change it as soon as possible before your model becomes a nightmare. Please try to follow Best Practices of model design for tabular cubes. If you don't, then your DAX will become more and more complex to the point where you'll not know what it calculates. Just a piece of good advice...

 

Best

Darek

Anonymous
Not applicable

First of all, your statements are not clear. You say you want the amount on the latest month of the latest year.... and then you say something about list price. Where the heck do you have a list price column in your tables?

 

When using FILTER, you should always remember that if you use a measure in the filtering condition, the measure performs a context transition because each measure is wrapped in CALCULATE implicitly. You should understand how context transition works before you even start using (measures in) FILTERs.

 

Here's probably what you want:

[Latest Sale Year] = MAX( Sales[Year] )

[Latest Sale Month] =
var __latestYear = [Latest Year]
var __latestMonth =
	CALCULATE(
		MAX( Sales[Month] ),
		Sales[Year] = __latestYear
	)
return
	__latestMonth
	
[Latest Amount] =
var __latestMonth = [Latest Month]
var __latestYear = [Latest Year]
var __latestAmount =
	CALCULATE(
		MAX( Sales[Amount] ),
		Sales[Year] = __latestYear,
		Sales[Month] = __latestMonth
	)
return
	__latestAmount

If you have selected many items, you'll get the maximum amount for any of the items in the set that have the same latest year and latest month. If you want this measure(s) to work only when one item is visible, then you must guard your measures with HASONEVALUE or HASONEFILTER.

 

Best

Darek

View solution in original post

Anonymous
Not applicable

Thank you so much, Darek. It works perfectly.
I'm noob in DAX.

 

Best regards.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors