cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gwright15 Regular Visitor
Regular Visitor

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 Established Member
Established Member

Re: Most recent value based on two variables

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

 

gwright15 Regular Visitor
Regular Visitor

Re: Most recent value based on two variables

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 149 members 1,599 guests
Please welcome our newest community members: