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.
Hello all,
Posting here again as I'm still unable to get an answer, sincerely appreciate all insights.
I would like to have a formula that creates the column "Opening Stock After" for the current Week which is derived from subtracting the the previous Week's Demand from the previous Week's Opening Stock After and adding the previous Week's Proposed Shipment. (e.g. for G52314 Week 34 Opening Stock After -> 113-70+40 = 83) (e.g. for G52314 Week 37 Opening Stock After -> 0-40+100=60).
However for the smallest week number (Week 31), since there is no data for the previous week, the Opening Stock After is always equal to the Opening Stock.
The Opening Stock After should never be a negative value (e.g. if value is negative, return 0 instead). If the previous week's Opening Stock After is negative, 0 should be used in the calculation of the current week's Opening Stock After (e.g. X78321 Week 37 calculation should be 0-49+60= 11).
Unique | Week Number | Opening Stock | Proposed Shipment | Demand | Opening Stock After |
G52314 | 31 | 120 | 0 | 35 | 120 |
G52314 | 32 | 120 | 70 | 42 | 85 |
G52314 | 33 | 120 | 40 | 70 | 113 |
G52314 | 34 | 120 | 40 | 130 | 83 |
G52314 | 35 | 120 | 80 | 140 | 0 |
G52314 | 36 | 120 | 100 | 40 | 0 |
G52314 | 37 | 120 | 40 | 36 | 60 |
X78321 | 31 | 200 | 0 | 57 | 200 |
X78321 | 32 | 200 | 20 | 48 | 143 |
X78321 | 33 | 200 | 90 | 120 | 115 |
X78321 | 34 | 200 | 60 | 150 | 85 |
X78321 | 35 | 200 | 60 | 150 | 0 |
X78321 | 36 | 200 | 60 | 49 | 0 |
X78321 | 37 | 200 | 60 | 52 | 11 |
how it looks like on excel formulas:
Once again, thank you for all the help.
Solved! Go to Solution.
@Anonymous
Please find attached modified sample file. Note that performance would be most probably bad.
Good morning @Anonymous
Please refer to attached sample file with the solution.
I have to mention that this solution is not perfect. There could be some cases where it might not provide correct results however, such cases are extremely rare. Anyway the solution is already complex and tying to improve further will result in even more complex and less efficient solution which I guess won't be necessary. Please let me know if you have any further questions.
Opening Stock After =
VAR CurrentWeek1 = Data[Week Number]
VAR OpeningStock1 = Data[Opening Stock]
VAR CurrentUniqueTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Unique] ) )
VAR StockAfterTable =
ADDCOLUMNS (
CurrentUniqueTable,
"@StockAfter",
VAR CurrentWeek2 = [Week Number]
VAR TableBefore = FILTER ( CurrentUniqueTable, [Week Number] < CurrentWeek2 )
VAR Value1 =
SUMX (
TableBefore,
[Proposed Shipment] - [Demand]
)
VAR Value2 = Value1 + OpeningStock1
RETURN
IF ( Value2 < 0, 0, Value2 )
)
VAR CurrentRecord = FILTER ( StockAfterTable, [Week Number] = CurrentWeek1 )
VAR PreviousRecord = FILTER ( StockAfterTable, [Week Number] = CurrentWeek1 - 1 )
VAR OpeningStock2 = MINX ( CurrentRecord, [@StockAfter] )
VAR Result =
IF ( OpeningStock2 = 0, SUMX ( PreviousRecord, [Proposed Shipment] - [Demand] ), OpeningStock2 )
RETURN
IF ( Result < 0, 0, Result )
Good morning @tamerj1
Thank you soooo much for your reply!! The code works almost perfectly, however I found that if i added more weeks after Week 37 the code doesn't return the right answers. I added in new data for Week 38 and Week 39 and experimented with the code.
The desired outcome with Week 38 and 39 data:
Unique | Week Number | Opening Stock | Proposed Shipment | Demand | Opening Stock After |
G52314 | 31 | 120 | 0 | 35 | 120 |
G52314 | 32 | 120 | 70 | 42 | 85 |
G52314 | 33 | 120 | 40 | 70 | 113 |
G52314 | 34 | 120 | 40 | 130 | 83 |
G52314 | 35 | 120 | 80 | 140 | 0 |
G52314 | 36 | 120 | 100 | 40 | 0 |
G52314 | 37 | 120 | 40 | 36 | 60 |
G52314 | 38 | 120 | 50 | 38 | 64 |
G52314 | 39 | 120 | 60 | 40 | 76 |
X78321 | 31 | 200 | 0 | 57 | 200 |
X78321 | 32 | 200 | 20 | 48 | 143 |
X78321 | 33 | 200 | 90 | 120 | 115 |
X78321 | 34 | 200 | 60 | 150 | 85 |
X78321 | 35 | 200 | 60 | 150 | 0 |
X78321 | 36 | 200 | 60 | 49 | 0 |
X78321 | 37 | 200 | 60 | 52 | 11 |
X78321 | 38 | 200 | 50 | 30 | 19 |
X78321 | 39 | 200 | 60 | 25 | 39 |
The result on PBI:
For Week 38 and 39 the code took the previous week's Proposed Shipment - Demand without adding in the previous week's Opening Stock After.
Thank you once again for your help thus far... 🙏
HI @Anonymous
I was able to get correct numbers ofr the added rows. However, I went further and added even more rows and realized that cycling between positive to negative to positive more than one time will make the DAX solution impossible to achieve. What can be achieved with a simple function in excel cannot be achieved with DAX. In general DAX does not support recursive calculations but in some cases there are workarounds apparently not in this case.
I see, thank you very much @tamerj1 for your generous help... If there is a work around, creating new measures or other calculated columns to achieve the desired result, I'm all ears...
@Anonymous
Isthere a maximum number of restart cycles?
@tamerj1
No, it is random depending on the Demand/Proposed Shipment for the current week as well as the previous week's Opening Stock After..
@Anonymous
I know its random but in general can we say this cannot happen for example more than 5 times or it can be much more?
@tamerj1
Actually yes, you could say the maximum restart cycles is the number of weeks that the current dataset spans over (e.g. Week 31-37, maximum restart cycles = 6).
Good morning @tamerj1
I've tried the code above and adjusted it accordingly to the maximum number of weeks that my dataset could span over and so far it works! I can't thank you enough for your big hearted help over the past few days... you made my week and I hope you have a great weekend ahead, thank you 😃
@Anonymous
I know its random but in general can we say this cannot happen for example more than 5 times or it can be much more?
@tamerj1
Or you could say the maximum restart cycles is the number of weeks that the current dataset spans over (e.g. Week 31-37, maximum restart cycles = 6).
Hi @Anonymous
when I reached the part when you said "If the previous week's Opening Stock After is negative, 0 should be used in the calculation of the current week's " I was like 😅
I don't think it's going to be easy. However, I have something in mind, let me try apply it tomorrow morning. Anyway, this is not going to be efficient by any means so I hope your data is not that big.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |