How to get the value corresponding to the earliest date of a table in DAX?

[matteomatilde]

Hello to everyone!

This is my table:

STK_ID	STK_ITM_ID	STK_QTY	STK_DATE
1	I0001	30	1 Jan 21
2	I0002	40	3 Jan 21
3	I0003	50	1 Jan 21
4	I0001	34	4 Jan 21
5	I0002	35	4 Jan 21

The result I would like to have is:

STK_ITM_ID	STK_QTY
I0001	30
I0002	40
I0003	50

How can I get the STK_QTY values, for each item, corresponding to the earliest dates?

Thank you in advance.

[Jihwan_Kim · Accepted solution]

Hi, @matteomatilde 

Please check the below picture and the sample pbix file's link down below.

qty total = SUM('Table'[STK_QTY])

STK QTY Earliest =

VAR groupbyitem =

GROUPBY (

'Table',

'Table'[STK_ITM_ID],

"earliest", MINX ( CURRENTGROUP (), 'Table'[STK_DATE] )

)

RETURN

CALCULATE (

[qty total],

FILTER ( 'Table', 'Table'[STK_DATE] = MINX ( groupbyitem, [earliest] ) )

)

STK QTY Earliest Total fix =

SUMX(VALUES('Table'[STK_ITM_ID]), [STK QTY Earliest])

https://www.dropbox.com/s/52jyxqwkcdnfk85/matteo.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[matteomatilde]

I'm sorry, but I've got another problem. The measures you gave me totally work when they are applied to the FACT_STK, but I can't make them work with the FACT_MOV, since I get the same date and quantities for all rows.

Can you help me?

https://www.dropbox.com/s/ksvc1jme2lb4jql/matteo.pbix?dl=0

Thank you so much!

[Jihwan_Kim]

Hi, @matteomatilde 

Thank you for your feedback.

Sorry that I cannot know how your desired outcome looks like.

Please correct me if I am wrong, but STK ids are not filtering MOV table, right?

[matteomatilde]

Oh yes, you are perfectly right, @Jihwan_Kim , sorry! By using the item ID from the MSTR_ITM it works fine.

However, I'm making these measures with the purpose of calculating the average stock of the four warehouses, as I explained in my latest question here in the forum, but I don't know if I am taking the right approach. If you have time, can you please check that post? Thank you so much and sorry, again! 

[matteomatilde]

Thank you!