Issue with Inventory Ageing Report

[Aditya_Mishra1]

We have data with Date column, Item group, Product, Purchase qty, sold qty. 
I'm calculating the age of qty for each Item Group and for their Products as Item group is a category and Products are their sub category as shown in the Screenshots, Base, Chemicals, Concentrate Chemicals are Item group and they have n number of products in them. The problem that I'm facing is I'm getting correct value at the Item level but not for the product level.

Sharing screenshots to help you understand the problem I'm facing.

The above screenshot shows the values for Item Groups that are Base, Chemical, Dipped bathi etc, here the values are correct.

In the above screenshot the values for Product level is coming false.

The DAX expression that I'm using is 

Zf_Aging Qty <30 Products2 =

VAR SaleSum =

    CALCULATE(

        SUM('Aging Main'[Sold Qty]),

        FILTER('Aging Main', 'Aging Main'[Date] <= MAX(DateTable[Date]))

    )

VAR Purch =

    CALCULATE(

        SUM('Aging Main'[Purchase Qty]),

        FILTER('Aging Main', (MAX(DateTable[Date]) - 'Aging Main'[Date]) > 30)

    )

VAR PurchSum =

    CALCULATE(

        SUM('Aging Main'[Purchase Qty]),

        FILTER('Aging Main', (MAX(DateTable[Date]) - 'Aging Main'[Date]) >=0 && (MAX(DateTable[Date]) - 'Aging Main'[Date]) <= 30)

    )

VAR Sale = -SaleSum // Sold qty had negative values

VAR IsItemLevel = HASONEVALUE('Aging Main'[Item Group])

RETURN

IF(

    IsItemLevel,

    IF(

        Purch >= Sale,

        IF(ISBLANK(PurchSum), 0, PurchSum),

        VAR SaleSum1 = Sale - Purch

        VAR AgingQty =

            IF(

                SaleSum1 >= PurchSum,

                0,

                IF(ISBLANK(PurchSum - SaleSum1), 0, PurchSum - SaleSum1)

            )

        RETURN AgingQty

    ),

    SUMX(

        VALUES('Aging Main'[Item Group]),

        [Z_Aging Qty <30]

    )

)

Please note that this DAX was provided by a member here only but it did not work as in last SUMX function the same name of the current measure name was there which gave me an error so I had to make a new measure Z_Aging Qty <30 with the same logic.
Please guide and any help or solution will be appreciated.

[Ashish_Mathur]

Hi,

Your question is not clear at all.  Share some data to work with, explain the question and show the expected result.

[Aditya_Mishra1]

Hi @Ashish_Mathur 
I'm sharing you the pbix file go through this and you'll understand the output I want.
https://drive.google.com/drive/folders/1fcJSFbn8EO7miFaWXx110C9FUZu2G8wr?usp=sharing

You'll find the file here. Please note Z_Inventory_Aging is the page I'm working on and the values in Item group are correct but the values in Product level is incorrect.

[v-xiandat-msft · Accepted solution]

Hi @Aditya_Mishra1 ,

According to your DAX formula, your purchsum value is blank, so the result is bound to be assigned a value of 0

When I changed the formula to the following case, I found that the values could be displayed

Z_Aging Qty <30 = 
VAR SaleSum=
CALCULATE(
    SUM('Aging Main'[Sold Qty]),
    FILTER('Aging Main',
    'Aging Main'[Date]<=MAX(DateTable[Date]))
)
VAR Purch=
CALCULATE(
    SUM('Aging Main'[Purchase Qty]),
    FILTER('Aging Main',
     (MAX(DateTable[Date])-'Aging Main'[Date])>30)

 )
VAR PurchSum=
CALCULATE
(
    SUM('Aging Main'[Purchase Qty]),
    FILTER('Aging Main',
     (MAX(DateTable[Date])-'Aging Main'[Date])>=0 || (MAX(DateTable[Date])-'Aging Main'[Date])<=30)

)
VAR Sale=-SaleSum
RETURN
IF(
    Purch>=Sale, IF(ISBLANK(PurchSum),0,PurchSum), 
    VAR SaleSum1= Sale-Purch
    var AgingQty =
 if(
    SaleSum1>=PurchSum,0,IF(ISBLANK(PurchSum-SaleSum1),0,PurchSum-SaleSum1)
    )
    Return AgingQty
)

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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

[Ashish_Mathur]

Hi,

That is a 100 MB file.  Please reduce the file size and share only what is important.

[v-xiandat-msft]

Hi @Aditya_Mishra1 ,

If you are convenient, you can send your PBIX file, thank you very much.

Best Regards,

Xianda Tang

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

[Aditya_Mishra1]

Hi @v-xiandat-msft 
I'm sharing you the pbix file 
https://drive.google.com/drive/folders/1fcJSFbn8EO7miFaWXx110C9FUZu2G8wr?usp=sharing

You'll find the file here. Please note Z_Inventory_Aging is the page I'm working on and the values in Item group are correct but the values in Product level is incorrect.