Need help please on DAX: Calculated Column

[Anonymous]

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.

[tamerj1]

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 )

[Anonymous]

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

[tamerj1]

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.

[Anonymous]

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

[tamerj1]

@Anonymous 

Isthere a maximum number of restart cycles?

[Anonymous]

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

[tamerj1]

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

[Anonymous]

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

[tamerj1 · Accepted solution]

 @Anonymous 
Please find attached modified sample file. Note that performance would be most probably bad.

[Anonymous]

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 😃

[tamerj1]

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

[Anonymous]

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

[tamerj1]

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. 

[Anonymous]

hi @tamerj1 

thank you for helping 🙏