cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Slow Moving Inventory in USD

Can anyone help me with a DAX on a logic to calculate the slow moving inventory in USD.

 

I have a Base Inventory value in USD (Base InvVal), a creation date, a consumption date.

An inventory is slow moving if its been 12-36 months without consumption. So, 12-36 months from creation date if there is no consumption date, but if there is a consumption date then 12-36 months wrt consumption date.

 

So, if A= [Date] - [Consumption date]

B= [date] - [Creation date]

 

 

Slow Moving is defined as A between (12-36 months) or B between ( 12-36 months).

 

I can write nested if statements to do:

 

SLow Moving USD = CALCULATE(

                                                        SUM(Base InvVal),   

                                                             IF(

                                                                 AND(A= NULL, IF(

                                                                                               AND(B>=12,B<=36),1,0), 1, IF...................................

 

This becomes very complicated. This is theoritical and not sure if it is valid even. I don't know if  I explained this correctly, but I am really struggling. Any help will be appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Continued Contributor
Continued Contributor

@atulj10

Here is my attempt to solve your problem. I am creating a range of dates for -12 - 36 months and comparing them to the dates in the dataset, if dates intersect then it's slowly moving stock and we can return the value.

 

 

 

SM Inventory Value = 
VAR SumOf = 
CALCULATE(
    SUMX(
        Table2,
        Table2[Quantity] * Table2[Value]
    )
)
VAR Range = DATESINPERIOD('Calendar'[Date], EDATE(TODAY(), -12), -24, MONTH)
RETURN
CALCULATE(
    IF(
        COUNTROWS(
        INTERSECT(
           VALUES(Table2[Consumption Date]),
           Range)
       ) > 0, 
       SumOf, 0
    )
)

 

 

image.png

View solution in original post

5 REPLIES 5
Highlighted
Continued Contributor
Continued Contributor

 

 

 

@atulj10

I assume you can refresh easily both PostingDate and Last ConsumtionDate from your ERP.

 

 pict222.PNG

This basic and simply solution can help you to calculate the seniority for each row.

 

 

IF (
    [LastConsum] = BLANK ();
    ( TODAY () - Sheet1[Posting Date] )
        / 365;
    IF (
        NOT ( ISBLANK ( Sheet1[LastConsum] ) );
        ( TODAY () - Sheet1[LastConsum] )
            / 365;
        BLANK ()
    )
)

 

Is that what you are   looking for?

 

 

 

 

 

If I can...
Highlighted

Thanks for the quick response

 

I understand you are calculating the date diff which satsifies my criteria. But, I am still trying to compare the date differences with the range 12-36 months. So if my date difference (in months) is in this range then my inventory in Slow moving. And then I am trying to add up the value for those inventory.

 

From the formula you have, I am getting the date diffs, but the complexity comes when I am trying to determine the slow moving logic.

Highlighted
Continued Contributor
Continued Contributor

@atulj10

 

 

pict223.PNG

 

of course we can add SlowMovIndex

 

SlowMovIndex = IF(Sheet1[Seniority] >=0 && Sheet1[Seniority] <1; 12;IF(Sheet1[Seniority] >=1 && Sheet1[Seniority] <2; 24;IF(Sheet1[Seniority] >=2 && Sheet1[Seniority] <=3; 36;BLANK())))

then SlowMovValue

 

 

SlowMovIndex = IF(Sheet1[Seniority] >=0 && Sheet1[Seniority] <1; 12;IF(Sheet1[Seniority] >=1 && Sheet1[Seniority] <2; 24;IF(Sheet1[Seniority] >=2 && Sheet1[Seniority] <=3; 36;BLANK())))

If this logic is ok, it is possible to combine the calculation

 

If I can...
Highlighted
Continued Contributor
Continued Contributor

@atulj10

 

Perhaps this one is closer to whato you are looking for

 

 

pic999.PNG

 

Plase check the logic in the column test1

 

Test1 = 
/* first IF check SlowMoving = 12 montshs */
IF (
    Sheet1[LastConsum] <> BLANK ()
        && Sheet1[SlowMovIndex] <> BLANK ()
        && (
            ( TODAY () - Sheet1[LastConsum] )
                / 365
        )
            > 0
        && (
            ( TODAY () - Sheet1[LastConsum] )
                / 365
        )
            < 1;
    Sheet1[Qty] * Sheet1[Value];
    /* second IF check SlowMoving = 24 months */
    IF (
        Sheet1[LastConsum] <> BLANK ()
            && Sheet1[SlowMovIndex] <> BLANK ()
            && (
                ( TODAY () - Sheet1[LastConsum] )
                    / 365
            )
                >= 1
            && (
                ( TODAY () - Sheet1[LastConsum] )
                    / 365
            )
                < 2;
        Sheet1[Qty] * Sheet1[Value];
        /* third IF check SlowMoving = 36 months */
        IF (
            Sheet1[LastConsum] <> BLANK ()
                && Sheet1[SlowMovIndex] <> BLANK ()
                && (
                    ( TODAY () - Sheet1[LastConsum] )
                        / 365
                )
                    >= 2
                && (
                    ( TODAY () - Sheet1[LastConsum] )
                        / 365
                )
                    < 3;
            Sheet1[Qty] * Sheet1[Value];
            BLANK ()
        )
    )
)

This solution could allow you to delete Seniority, SlowMovIndex and Test

 

Regards

If I can...
Highlighted
Continued Contributor
Continued Contributor

@atulj10

Here is my attempt to solve your problem. I am creating a range of dates for -12 - 36 months and comparing them to the dates in the dataset, if dates intersect then it's slowly moving stock and we can return the value.

 

 

 

SM Inventory Value = 
VAR SumOf = 
CALCULATE(
    SUMX(
        Table2,
        Table2[Quantity] * Table2[Value]
    )
)
VAR Range = DATESINPERIOD('Calendar'[Date], EDATE(TODAY(), -12), -24, MONTH)
RETURN
CALCULATE(
    IF(
        COUNTROWS(
        INTERSECT(
           VALUES(Table2[Consumption Date]),
           Range)
       ) > 0, 
       SumOf, 0
    )
)

 

 

image.png

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors