cancel
Showing results for
Did you mean:
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 IV

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

6 REPLIES 6
Super User III

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

Regular Visitor

Hi @Raimana ,

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

Super User III

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 IV

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.