Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hot_Potato
Frequent Visitor

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.

Cum_Sum = CALCULATE(SUM(ILE[Quantity]),ALLSELECTED('Calendar'),'Calendar'[Date]<=MAX(ILE[Posting_Date]))
 
Thank you in advance!😁
Below is what happens when I use this formula:
This is using my DAX formulaThis is using my DAX formulaThis is what I am trying to achieveThis is what I am trying to achieve

 

8 REPLIES 8
TheoC
Super User
Super User

Hi @Hot_Potato 

 

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.

 

TheoC_1-1685479970523.png

 

 

All the best!

 

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@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. Power BI Forum.jpg

Hi @Hot_Potato 

 

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  

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

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

 

  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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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_DateEntry_TypeItem_NoVariant_CodeLocation_CodeLot_NoQuantityExpiration_DateProduction DateAge by Posting DateAge Group-By Posting Date
17-Jun-17Output600R6688EFG1000049014-Jun-2014-Jun-170Less than 3 months
18-Jun-17Output600R6688EFG1000029414-Jun-2014-Jun-170Less than 3 months
19-Jun-17Output600R6688EFG1000039214-Jun-2014-Jun-170Less than 3 months
20-Jun-17Output600R6688EFG1000039214-Jun-2014-Jun-170Less than 3 months
21-Jun-17Output600R6688EFG1000039214-Jun-2014-Jun-170Less than 3 months
22-Jun-17Output600R6688EFG1000039214-Jun-2014-Jun-170Less than 3 months
27-Jun-17Sale600R6688EFG10000-19627-Jun-2027-Jun-170Less than 3 months
02-Jul-17Sale600R6688EFG10000-1402-Jul-2002-Jul-170Less than 3 months
03-Jul-17Sale600R6688EFG10000-81703-Jul-2003-Jul-170Less than 3 months
04-Jul-17Sale600R6688EFG10000-14004-Jul-2004-Jul-170Less than 3 months
05-Jul-17Sale600R6688EFG10000-40105-Jul-2005-Jul-170Less than 3 months
12-Jul-17Sale600R6688EFG10000-16812-Jul-2012-Jul-170Less than 3 months
27-Jul-17Sale600R6688EFG10000-16827-Jul-2027-Jul-170Less than 3 months
13-Aug-17Sale600R6688EFG10000-44813-Aug-2013-Aug-170Less than 3 months
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi I don't have link for the PBIX File but above is the sample data. Thanks.

TheoC
Super User
Super User

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

 

  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 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.