Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Aditya_Mishra1
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.

Sharing screenshots to help you understand the problem I'm facing.

Screenshot 2024-01-12 121823.png

The above screenshot shows the values for Item Groups that are Base, Chemical, Dipped bathi etc, here the values are correct.

 

Screenshot 2024-01-12 121932.png

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

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

vxiandatmsft_0-1705886958613.pngvxiandatmsft_1-1705886990704.png

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:

vxiandatmsft_2-1705887062222.png

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.

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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

vxiandatmsft_0-1705886958613.pngvxiandatmsft_1-1705886990704.png

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:

vxiandatmsft_2-1705887062222.png

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,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-xiandat-msft
Community Support
Community Support

Hi @Aditya_Mishra1 ,

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.

Hi @v-xiandat-msft 
I'm sharing you the pbix file 
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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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