Forum Discussion
Remaining Balance per year with dynamic age grouping
Hi, I am trying to create a bar chart per month & year of the remaining stocks per year with the age of that stocks on that particular period. The age of my stocks were computed based on the production date and posting date - this means that the remaining stocks were changing its age each month and year. Any tips how to achieve this in power bi?
I have tried below dax, but it is giving me a negative value whenever I add a Aged Group bracket on the legend.
8 Replies
- TheoC
Community Champion
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):
- 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 🙂
- Create a new measure to calculate the age of your stock. Based on what you said, it should be calculated as:
- Ashish_Mathur
Super User
Hi,
Share the download link of the PBI file.
- TheoC
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
- Create a new measure to calculate the age of your stock. Based on what you said, it should be calculated as:
- TheoC
Hi 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
