cancel
Showing results for
Did you mean:
Highlighted
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

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

```

5 REPLIES 5
Highlighted
Continued Contributor

@atulj10

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?

If I can...
Highlighted
Helper I

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

@atulj10

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

@atulj10

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

If I can...
Highlighted
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
)
)

```

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

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