Inventory Aging Report

[aditya_141228]

I'm stuch with this Inventory Aging report for more than a month, I've tried so many DAX expressions and what not. I'm sharing the link to the pbix file. Kindly look into it and help me out.

There are two pages in the report, refer Page 1 (Inventory aging) for verification and make similar dashboard in Page 2 (Z_Inventory _Aging). So in Page 1-

So here Base, Chemicals and Concentrate chemicals are Item Group and every item group has some number of Products. In this report the values that are coming at Item Group level is correct i.e., Aging 0-30Qty for Base 20537.32 is correct and each value for Item Group is correct however when we maximise the item group to views products in them the values at the Product level comes wrong.

Also The total at the bottom is wrong that needs to be sorted too.

Now in Page 2, To all total at the bottom is correct and similar to Page 1 values at the Item Level are correct but not for the Product Level.

The DAX measure which I'm using currently will be in the file however here is a different DAX measure that I used earlier just for reference I'm sharing

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
)

Please note whatever changes needs to be done should be done in Page 2.

I'm sharing the link for this report. Any help and support will be very much appreciated.

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

Thank You So Much, would mean a lot if any help is possible.

[Anonymous · Accepted solution]

Hi @aditya_141228 ,

Based on your description, your matrix totals on page 2 are correct, then you can create a new measure.

Measure = SUMX(VALUES('Aging Main'[Item Group]),[Aging > 180 Qty])

Best Regards,

Neeko Tang

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