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

 

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

     

5 Replies

  • gpiero's avatar
    gpiero
    Skilled Sharer
    9 years ago

     

     

     

    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'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
        Skilled 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

         

  • nickchobotar's avatar
    nickchobotar
    Skilled Sharer
    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
    )
)