Forum Discussion
Remaining Balance per year with dynamic age grouping
Hi @Hot_Potato
If you haven't established a Date table, please ensure to have one created.
Also, without dummy data, it's hard to provide a solution. However, based on what you have provided, you can try something like the following (please ensure your table names and column names are updated):
- Create a new measure to calculate the age of your stock. Based on what you said, it should be calculated as:
Stock Age = DATEDIFF ( ILE[Production_Date] , ILE[Posting_Date] , MONTH ) - Create a second measure which is closely aligned to what you had but takes it one step further:
Stock Count = CALCULATE ( SUM ( ILE[Quantity] ) , FILTER ( ALLSELECTED ( 'Date' ) , Date[Date] <= MAX ( ILE[Posting_Date] ) ) , Date[Date] = MAX ( Date[Date] ) ) - SUMX ( VALUES ( Date[Date] ) , CALCULATE ( SUM ( ILE[Quantity] ) ) ) - Using the column chart visual, drag Stock Count in the Values field and the Date table's Month column in the X Axis. You can add the Stock Age measure to the Legend field to split the aging. However, I'd suggest grouping the ages.
Note, if you want to use grouping for ages, you might want to create a Calculated Column for Stock Age rather than a measure (i.e. step 1). You can then group stock by age grouping using a SWITCH TRUE formula in a second calculated column.
Hope this helps and hope it works mate!
Theo 🙂
Hi TheoC , thank you for checking this. I have tried your dax formula, however it is still showing negative quantities. What I want to achieve is that, the report should get first the remaining quantity for the year or month and then categories it to age group by that particular year, same as in the bar graph that I have posted here. I have attached here a sample data for your reference. I hope you can help me solve it. Thanks.
Posting_Date Entry_Type Item_No Variant_Code Location_Code Lot_No Quantity Expiration_Date Production Date Age by Posting Date Age Group-By Posting Date 17-Jun-17 Output 600R 6688 E FG10000 490 14-Jun-20 14-Jun-17 0 Less than 3 months 18-Jun-17 Output 600R 6688 E FG10000 294 14-Jun-20 14-Jun-17 0 Less than 3 months 19-Jun-17 Output 600R 6688 E FG10000 392 14-Jun-20 14-Jun-17 0 Less than 3 months 20-Jun-17 Output 600R 6688 E FG10000 392 14-Jun-20 14-Jun-17 0 Less than 3 months 21-Jun-17 Output 600R 6688 E FG10000 392 14-Jun-20 14-Jun-17 0 Less than 3 months 22-Jun-17 Output 600R 6688 E FG10000 392 14-Jun-20 14-Jun-17 0 Less than 3 months 27-Jun-17 Sale 600R 6688 E FG10000 -196 27-Jun-20 27-Jun-17 0 Less than 3 months 02-Jul-17 Sale 600R 6688 E FG10000 -14 02-Jul-20 02-Jul-17 0 Less than 3 months 03-Jul-17 Sale 600R 6688 E FG10000 -817 03-Jul-20 03-Jul-17 0 Less than 3 months 04-Jul-17 Sale 600R 6688 E FG10000 -140 04-Jul-20 04-Jul-17 0 Less than 3 months 05-Jul-17 Sale 600R 6688 E FG10000 -401 05-Jul-20 05-Jul-17 0 Less than 3 months 12-Jul-17 Sale 600R 6688 E FG10000 -168 12-Jul-20 12-Jul-17 0 Less than 3 months 27-Jul-17 Sale 600R 6688 E FG10000 -168 27-Jul-20 27-Jul-17 0 Less than 3 months 13-Aug-17 Sale 600R 6688 E FG10000 -448 13-Aug-20 13-Aug-17 0 Less than 3 months