Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
This is the answer that worked for me:
https://stackoverflow.com/questions/38570755/min-value-with-group-by-in-power-bi-desktop
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.
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
Hi @Raimana ,
I was also looking for a similar solution. Could you please let me know what worked for you?
Hi,
I am not sure of what you mean but if my reply helped, please mark it as Answer.
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'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!
Thanks. the add column works as excepted but this doesn't aggregates well.
User | Count |
---|---|
93 | |
83 | |
77 | |
70 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |