cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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.

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.
1 ACCEPTED SOLUTION
Community Support

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 6
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

I'm sharing you the pbix file go through this and you'll understand the output I want.

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.

Community Support

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.

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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.

Frequent Visitor

Hi @v-xiandat-msft
I'm sharing you the pbix file

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors