Need some help on my Aging calculation. I following the tricks from Sam here on create a table for the grouping and then create measure from the matrix placement.
But I don;t know why it doesn’t put at the correct group as it is always falls into 1st group.
The step is like this :
- In my transaction, based on field Transaction date I created this measure ->
Aging = CALCULATE(
DATEDIFF( SELECTEDVALUE(Trans[TRANSDATE]), NOW(), DAY ), ALLSELECTED('Dates'[DATE_])
One reason I’m using ALLSELECTED in the end is actually I made the date filter to be adjustable, but this is another story, and not an issue.
- Because originally I’m using Direct Query, I have to use TempTable inside my “placement” measure like below ->
Age QTY = VAR TmpTable = ADDCOLUMNS( VALUES(Trans[ITEMID]), "TmpAging", CALCULATE( [Aging], CALCULATETABLE( VALUES(Trans[ITEMID]) ), ALLSELECTED() ) ) RETURN CALCULATE( SUM(Trans[BALQTY]), FILTER( TmpTable, COUNTROWS( FILTER('Interval', [TmpAging] >= 'Interval'[FROM] && [TmpAging] <= 'Interval'[TO] ) ) > 0 ) )
I’ve put my pbix for reference and changed the mode to import mode in drop box in this link ->
Hope the file can be downloaded and use.
Much thanks in advance,