Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello there,
I have a table that consists of the columns below, 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), negative values should never be used in the calculation of Opening Stock After.
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 |
formula view:
Greatly appreciate any insights on this please, thank you.
Solved! Go to Solution.
Hi, @Anonymous
You can try the following methods.
Column:
New 1 = [Proposed Shipment]-[Demand]
New 2 =
VAR N2 =
[Opening Stock]
+ SUMX (
FILTER (
ALL ( 'Table' ),
[Unique] = EARLIER ( 'Table'[Unique] )
&& [Week Number] < EARLIER ( 'Table'[Week Number] )
),
[New 1]
)
RETURN
IF ( N2 < 0, 0, N2 )
New 3 =
VAR PrevN1 =
CALCULATE (
SUM ( 'Table'[New 1] ),
FILTER (
'Table',
[Week Number]
= EARLIER ( 'Table'[Week Number] ) - 1
&& [Unique] = EARLIER ( 'Table'[Unique] )
)
)
RETURN
IF ( [New 2] > 0, [New 2], IF ( [New 2] + PrevN1 < 0, 0, [New 2] + PrevN1 ) )
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Jihwan_Kim Hello Jihwan, if possible I would deeply appreciate your insights on this please. It relates to the same problem that you had previously answered to. Thank you so much!!
@Anonymous , Based on what I got, Try new column like
New column =
[Opening Stock] + Sumx(filter(Table, [Unique] =earlier([Unique]) && [Week Number] < earlier([Week Number])), [Stock Proposed]- [Shipment Demand])
Hello amit,
Thank you for your reply. However, I did not get the desired result. The new column returned the following values:
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 | -7 |
G52314 | 36 | 120 | 100 | 40 | -67 |
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 | -5 |
X78321 | 36 | 200 | 60 | 49 | -95 |
X78321 | 37 | 200 | 60 | 52 | -84 |
The Opening Stock After should not have a 0 value and 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 calculation should be 0-49+60= 11 and not -49-49+60= -84.
Desired result:
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 |
Hi, @Anonymous
You can try the following methods.
Column:
New 1 = [Proposed Shipment]-[Demand]
New 2 =
VAR N2 =
[Opening Stock]
+ SUMX (
FILTER (
ALL ( 'Table' ),
[Unique] = EARLIER ( 'Table'[Unique] )
&& [Week Number] < EARLIER ( 'Table'[Week Number] )
),
[New 1]
)
RETURN
IF ( N2 < 0, 0, N2 )
New 3 =
VAR PrevN1 =
CALCULATE (
SUM ( 'Table'[New 1] ),
FILTER (
'Table',
[Week Number]
= EARLIER ( 'Table'[Week Number] ) - 1
&& [Unique] = EARLIER ( 'Table'[Unique] )
)
)
RETURN
IF ( [New 2] > 0, [New 2], IF ( [New 2] + PrevN1 < 0, 0, [New 2] + PrevN1 ) )
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
77 | |
76 | |
44 | |
31 | |
26 |
User | Count |
---|---|
97 | |
89 | |
52 | |
48 | |
46 |