Forum Discussion
Issue with Inventory Ageing Report
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.
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.
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.