Forum Discussion
Remaining Balance per year with dynamic age grouping
Community ChampionHi Anonymous
Attached is a PBIX which has the outputs you're after (I hope). Obviously, as you add more data to the attached PBIX model, the output will look more detailed, etc.
All the best!
Theo
TheoC hi! thank you for checking this, however this did not solve the problem. The total of the remaining quantity did not sum up to 0 though. The adjusted qty column measure that you have put just converted the number from negative to positive making both the sales & output both positive, hence you were able to come up to a bar chart with no negative number. However what I need is to get the balance per year before it gets 0 with the dynamic ageing groups.
- TheoC
Hi Anonymous
I'm sorry, I do not understand exactly what you are after.
Can you please manually enter the output that you want in the "OUTPUT REQUIRED" column in the below data sample.
Entry_Type Item_No Variant_Code Location_Code Lot_No Quantity Posting_Date Expiration_Date Production Date Age by Posting Date Adjusted Quantity Cumulative Quantity End Of Month Age By Posting Date (Grouped) OUTPUT REQUIRED Output 600R 6688 E FG10000 490 17/06/2017 14/06/2020 14/06/2017 3 490 490 30/06/2017 3 - 4 months Output 600R 6688 E FG10000 294 18/06/2017 14/06/2020 14/06/2017 4 294 784 30/06/2017 4 - 6 months Output 600R 6688 E FG10000 392 19/06/2017 14/06/2020 14/06/2017 5 392 1176 30/06/2017 4 - 6 months Output 600R 6688 E FG10000 392 20/06/2017 14/06/2020 14/06/2017 6 392 1568 30/06/2017 6+ months Output 600R 6688 E FG10000 392 21/06/2017 14/06/2020 14/06/2017 7 392 1960 30/06/2017 6+ months Output 600R 6688 E FG10000 392 22/06/2017 14/06/2020 14/06/2017 8 392 2352 30/06/2017 6+ months Sale 600R 6688 E FG10000 -196 27/06/2017 27/06/2020 27/06/2017 0 196 2548 30/06/2017 Less than 3 months Sale 600R 6688 E FG10000 -14 2/07/2017 2/07/2020 2/07/2017 0 14 2562 31/07/2017 Less than 3 months Sale 600R 6688 E FG10000 -817 3/07/2017 3/07/2020 3/07/2017 0 817 3379 31/07/2017 Less than 3 months Sale 600R 6688 E FG10000 -140 4/07/2017 4/07/2020 4/07/2017 0 140 3519 31/07/2017 Less than 3 months Sale 600R 6688 E FG10000 -401 5/07/2017 5/07/2020 5/07/2017 0 401 3920 31/07/2017 Less than 3 months Sale 600R 6688 E FG10000 -168 12/07/2017 12/07/2020 12/07/2017 0 168 4088 31/07/2017 Less than 3 months Sale 600R 6688 E FG10000 -168 27/07/2017 27/07/2020 27/07/2017 0 168 4256 31/07/2017 Less than 3 months Sale 600R 6688 E FG10000 -448 13/08/2017 13/08/2020 13/08/2017 0 448 4704 31/08/2017 Less than 3 months