Product Aging Life

[Velvetine27]

Hi guys,

I have two tables which look like this :

Table A :

SKU	PostingDate	QtyMovement	Batch
101	13/1/2022	1000	ABC123
101	15/1/2022	-50	ABC123
101	20/1/2022	-30	ABC123
102	13/1/2022	2000	ABC123
102	14/1/2022	-100	ABC123

Table B :

SKU	Batch	BatchReleaseDate
101	ABC123	13/1/2022
101	BCD234	22/2/2022
102	ABC123	13/1/2022

I'm looking for a way to calculate aging life of the remaining products. Say user selects a date at 1/8/2022 and Aging > 180 days, it should display how many Qty remaining for each SKU that are released more than 180 days ago. I created a calculated column combining the material and batch (KEYBatch) to link the two tables. I'm assuming the DAX looks something like this :

var 180days = CALCULATE(MAX('DATE'[Date]) - 180)

return = CALCULATE(SUM('Table A'[QtyMovement]), FILTER(DATE, DATE[Date] < 180days))

Hope you guys can help me. Thanks very much in advance !

[FreemanZ]

Are you expecting a measure? If so how would it be presented?

How is the Date table related to Table A and B?

[Velvetine27]

It doesnt have to be a measure, just anyway possible to calculate it. I do have another Date table derives from Table A [PostingDate], which is connected to both Table A and B

[FreemanZ]

it seems Table A, Table B and Date Table form a closed loop. How does it look like? i mean the filtering directions between them.

when you say aging, which date is it compared with, SKU PostingDate or BatchReleaseDate?

[Velvetine27]

The relationship table looks like this :

Date > Table A [Posting Date] & Table A[KeyBATCH] < Table B[KeyBatch]

Perhaps the relationship itself is a problem ?

Aging should compare MAX date with BatchReleaseDate, and then compute all transactions in Table A from the first PostingDate where it will be equal to BatchReleaseDate until the MAX date. 

[Velvetine27]

So I called the Table B[BatchReleaseDate] to Table A. I wrote this query :

var aging180 = CALCULATE(MAX('DATE'[Date]) - 180
return
CALCULATE SUM(Table A[QtyMovement], FILTER(Table A, Table A[BatchReleaseDate]<aging180))
This query doesn't return all results for the PostingDate until today. It somehow only sums few quantity which Im not quite sure what it is filtering.

Please help ! Thank you

[v-rongtiep-msft · Accepted solution]

Hi @Velvetine27 ,

Measure = var aging180 = CALCULATE(MAX('DATE'[Date]) - 180)
return 
CALCULATE(SUM('Table A'[QtyMovement]), FILTER('Table A', 'Table A'[PostingDate]<aging180))

The aging180 returns a  half a year before the selected date. When you select 2022/1/8, it would return 2021/7/12. And the "CALCULATE(SUM('Table A'[QtyMovement]), FILTER('Table A', 'Table A'[PostingDate]<aging180))"returns QtyMovement of Table A and PostingDate is earlier than 2021/7/12 .

In the table A, there is no date earlier than 2021/7/12. So it returns blank. You can change the measure.

Measure = var aging180 = CALCULATE(MAX('DATE'[Date]) - 180)
return 
CALCULATE(SUM('Table A'[QtyMovement]), FILTER('Table A', 'Table A'[PostingDate]>aging180))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.