Forum Discussion
Need help please on DAX: Calculated Column
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...