Dynamic inventory ageing

[amalsp_ha]

Hi All,

I am tring to calculate the Inventory ageing dynamically. I have a table with all the transactions (purchases and sales). I want to calculate the balance of the purchase as at a date and age this into buckets. I have got to a stage where I have got the balance of the purchase (M_Balance in the attached pbix) as at a date and the age of the remaing quantity (M_Age in the attached pbix). What I want to do is the group this into buckets in a column chart. Can anyone help with this?

The work I have done upto now is in the pbix at the following link. 

https://1drv.ms/u/s!Ap4-j7F8BaMh90Zk2O4jkeOg42Vn?e=cGtTKN

Thanks

Amal

[v-yangliu-msft]

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.

[amalsp_ha]

Hi,

Thanks for the reply. Could you do this in the attached file and show me. 

Amal