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