Forum Discussion
Helper IProduct Aging Life
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 !
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.
6 Replies
FreemanZSuper User
Are you expecting a measure? If so how would it be presented?
How is the Date table related to Table A and B?
Velvetine27It 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?