Slow Moving Inventory in USD

[Anonymous]

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.

[nickchobotar · Accepted solution]

@Anonymous

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
    )
)

 

[gpiero]

@Anonymous

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

 

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?

[Anonymous]

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.

[gpiero]

@Anonymous

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

[gpiero]

@Anonymous

Perhaps this one is closer to whato you are looking for

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