DAX first value of group

[Anonymous]

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.

Thanks for your help.

[iangarretson]

This is the answer that worked for me:

https://stackoverflow.com/questions/38570755/min-value-with-group-by-in-power-bi-desktop

[Ashish_Mathur]

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.

[Anonymous]

Thanks @Greg_Deckler and @Ashish_Mathur for your answers.

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

[Anonymous]

Hi @Anonymous ,

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

[Ashish_Mathur]

Hi,

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

[Greg_Deckler · Accepted solution]

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. 

[iangarretson]

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!

[Anonymous]

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