Remaining Balance per year with dynamic age grouping

Hi Anonymous 

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):

1. 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 )
   
   
2. 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] ) ) )
   
   
3. 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 🙂