Remaining Balance per year with dynamic age grouping

[Hot_Potato]

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 what I am trying to achieve

[TheoC]

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.

All the best!

Theo

[Hot_Potato]

@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 @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

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

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 🙂

[Hot_Potato]

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Hot_Potato]

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

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

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 🙂