cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gwright15
Helper I
Helper I

Most recent value based on two variables

Good evening

 

I have looking to create a formula to find the latest stock valuation on a product by product basis.

 

I have a field which identifies the last date and time, which i have used for a filter.  However, we can have multiple transaction recorded with the same date and time.

 

We have a second field call unique id.  However, this value is reset every month then.

 

Therefore, i need to understand if we can have a dax formula to identify the max date, and then the max unique ID.

 

By current formula is:

 

Current_Valuation = calculate(SUMX(Stock_Value,Stock_Value[newcost]),filter(Stock_Value,Stock_Value[New Date]=MAX(Stock_Value[New Date])))

I have tried this formula, but as some of the unique id can be higher in other periods, it doesn't always return a value.  For example on 31 July 2019, as unique id could be 456678, but on 1 August, the unique id resets, and now could read 345.

 

Current_Valuation = calculate(SUMX(Stock_Value,Stock_Value[newcost]),filter(Stock_Value,Stock_Value[New Date]=MAX(Stock_Value[New Date]) && Stock_Value[uniqueno]=max(Stock_Value[uniqueno])))

 

 

2 REPLIES 2
SteveCampbell
Memorable Member
Memorable Member

If I understand, you're trying to get the latest date, and the max id associated with that date?

Using variables will make this easier to read. Additionally, SUMX is a row iterator, and can be quite slow on large data sets.

 

Try this:

 

Current_Valuation =
var _MaxDate = MAX(Stock_Value[New Date]) var _MaxID = CALCULATE(MAX(Stock_Value[uniqueno]),Stock_Value[New Date] = _MaxDate) RETURN CALCULATE(SUM(Stock_Value[newcost]) ,Stock_Value[New Date]=_MaxDate ,Stock_Value[uniqueno]=_MaxID )

 

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

Remember to spread knowledge in the community when you can! tu.png

 



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Thank you - this helps to select the right values.  I have used the same fomula to calculate by quantity also:

 

Current_Quantity = 
var _MaxDate = MAX(Stock_Value[New Date]) 
var _MaxID = CALCULATE(MAX(Stock_Value[uniqueno]),Stock_Value[New Date] = _MaxDate) 
RETURN 
CALCULATE(SUM(Stock_Value[lev]) ,Stock_Value,Stock_Value[New Date]=_MaxDate,Stock_Value[uniqueno]=_MaxID )

It does create a couple of additional queries:

 

Firstly, the sum correctly - for example, total quantity states 50, rather than something 417,380

 

Secondly, they a filter the data by a product type rather than a stock code, this doesn't work.  Is this were SUMX would work.

 

Can you help me?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors