Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 s...
Need Help
  • nickchobotar's avatar
    nickchobotar
    9 years ago

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

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
gpiero
Icon for Skilled Sharer rankSkilled Sharer
9 years ago

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's avatar
    gpiero
    Icon for Skilled Sharer rankSkilled Sharer
    9 years ago

    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