Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 ) )
@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
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?
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.
@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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |