Need Help with Aging Report

[aditya_141228]

I've been working on this Inventory Aging Report, 

Here Base, Chemicals, Concentrate Chemicals are Item and every Item has multiple Products. The data that is coming here on Item Level for each bucket of 0-30, 31-60, 61-90 and so on "is correct", however when we expand the Items to view their respective products the data is wrong for Products i.e., the data that comes for products of a Item Group does not add up to the value coming for that Item Group.

Also at the bottom the Total is coming wrong too.

The DAX expression that I'm using is-

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

)

To Summarize my problem here-

• The data is coming right for Item Group level but not for Product Level, i.e., the summation of the values for product does not adds up to the value of their Item.
• The Total at the bottom is also coming wrong.

Please Note-
Data is correct for every Item Group level i.e., for Base the value of qty for the bucket 0-30 days 20537.32 is right and so is for every Item and for every bucket. the values that are coming for the respective products is wrong and the total is wrong. So for verification use the Item Group and if the values of products adds upto the value of their respective Item Group then the DAX is correct.

I'm sharing the link of this pbix file too for any references.

https://drive.google.com/drive/folders/1scttjjv8_mQBMyEtiUWY2kDJyp_qE_uf?usp=sharing

Any assistance and efforts will be appreciated, please do help me out with this issue. Thank You

[Ashish_Mathur · Accepted solution]

Hi,

This measure will get the correct figure for Z_aging Qty < 30.  See the last column in the image

Measure = SUMX(VALUES('Aging Main'[Product]),[Z_Aging Qty <30])

Hope this helps.

[v-binbinyu-msft]

Hi @aditya_141228 ,

Suggested Solutions:

1. Review Context Transition: Ensure that the DAX expressions correctly handle context transitions when moving from item group aggregations to product-level details. This includes verifying that the CALCULATE function is being used appropriately to modify filter contexts as needed.

2. Verify Aggregation Logic: Double-check the logic used for aggregating sales and purchase quantities, especially how the date filters are applied. It's crucial that the aggregation logic aligns with the hierarchical data model of item groups and products.

3. Total Calculation Review: For the total calculation issue, ensure that the DAX expression for totals is correctly scoped to include all relevant data points across both item groups and products. This might involve revisiting how totals are calculated and ensuring that the aggregation encompasses all necessary levels of data.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.