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

Frequent Visitor

## DAX first value of group

Hi,

I've been trying to get the the "Inital Stock" column and aggregate it by product :

 ProductID Date Stock EntryDate InitialStock A 15/09/2018 10 15/09/2018 10 A 16/09/2018 9 15/09/2018 10 A 17/09/2018 5 15/09/2018 10 A 18/09/2018 4 15/09/2018 10 A 19/09/2018 2 15/09/2018 10 A 20/09/2018 0 15/09/2018 10 B 12/12/2018 4 12/12/2018 4 B 13/12/2018 2 12/12/2018 4 B 14/12/2018 2 12/12/2018 4 C 01/01/2019 20 01/01/2019 20 C 02/01/2019 20 01/01/2019 20 C 03/01/2019 20 01/01/2019 20 C 04/01/2019 10 01/01/2019 20 C 05/01/2019 10 01/01/2019 20 C 06/01/2019 10 01/01/2019 20 C 07/01/2019 10 01/01/2019 20 C 08/01/2019 10 01/01/2019 20 C 09/01/2019 5 01/01/2019 20 C 10/01/2019 5 01/01/2019 20 C 11/01/2019 5 01/01/2019 20

I calcuted the column EntryDate using:

CALCULATE(Min(Table[Date]);ALLEXCEPT(Table;Table[ProducID]))

How can i get the EntryDate's Stock (ie InitialStock) reapeated across all dates.

Also, from that column i need to be able to aggregate by product like so :

 A 10 B 4 C 20 Total 34

I wonder this can easily be been via DAX.

1 ACCEPTED SOLUTION
Super User

You are going to want something like:

`InitialStock = MAXX(FILTER(ALL('Table13'),[ProductID] = EARLIER([ProductID]) && [Date] = EARLIER([EntryDate])),[Stock])`

That's a column formula.

See Table 13 of attached.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
8 REPLIES 8
Regular Visitor
Super User

Hi,

You do not need the Initial Stock and EntryDate columns at all.  This measure will work

=SUMX(SUMMARIZE(VALUES(Data[ProductID]),[ProductID],"ABCD",LOOKUPVALUE(Data[Stock],Data[Date],FIRSTNONBLANK(Data[Date],SUM(Data[Stock])))),[ABCD])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

The solution you provided only solved one aspect of my problem but it definitely gives me ideas to go further. Thanks again

Anonymous
Not applicable

Hi @Raimana ,

I was also looking for a similar solution. Could you please let me know what worked for you?

Super User

Hi,

I am not sure of what you mean but if my reply helped, please mark it as Answer.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

You are going to want something like:

`InitialStock = MAXX(FILTER(ALL('Table13'),[ProductID] = EARLIER([ProductID]) && [Date] = EARLIER([EntryDate])),[Stock])`

That's a column formula.

See Table 13 of attached.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

I'm trying to solve this problem but its that I have to find the entry date, not the stock at the entry date. How would I find the earliest date from the ProductIDDate?

Thank you!

Frequent Visitor

Thanks. the add column works as excepted but this doesn't aggregates well.