Forum Discussion

Aditya_Mishra1's avatar
Aditya_Mishra1
Frequent Visitor
2 years ago
Solved

Issue with Inventory Ageing Report

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.
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

6 Replies