Dynamic inventory ageing

Hi  amalsp_ha ,

You can group by the defined range for each [Bucket] by creating a computed column as follows.

Bucket_left =
IF(
   'Age_Bucket'[SortOrder] = 0 ,0,
    IF(
        'Age_Bucket'[SortOrder]=9,VALUE( RIGHT('Age_Bucket'[Bucket],3))+1,
    VALUE(
LEFT('Age_Bucket'[Bucket],
LEFT(
SEARCH("-",'Age_Bucket'[Bucket])-2)))))

Bucket_right =
IF(
   'Age_Bucket'[SortOrder] = 0 ,0,
    IF(
        'Age_Bucket'[SortOrder]=9,10000,
    VALUE(
RIGHT('Age_Bucket'[Bucket],
LEFT(
SEARCH("-",'Age_Bucket'[Bucket])-2)))))

Then you can use the SUMMARIZE() function to create a virtual table from the data in the visual table, and then use the selectcolumn() function in the virtual table to treat [M_balance] in the virtual table as a column data

Then go to the table Age_Bucket to compare with [Bucket_left] and [Bucket_right] to count.

SELECTCOLUMNS function (DAX) - DAX | Microsoft Docs

SUMMARIZE function (DAX) - DAX | Microsoft Docs

Best Regards,

Liu Yang

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