Forum Discussion
Helper IProduct Aging Life
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.
Super UserAre you expecting a measure? If so how would it be presented?
How is the Date table related to Table A and B?
Helper IIt 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