Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

DAX: Create new column with values that is dependant on previous rows

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.

 

UniqueWeek NumberOpening StockProposed ShipmentDemandOpening Stock After
G5231431120035120
G5231432120704285
G52314331204070113
G52314341204013083
G5231435120801400
G5231436120100400
G5231437120403660
X7832131200057200
X78321322002048143
X783213320090120115
X78321342006015085
X7832135200601500
X783213620060490
X7832137200605211

 

formula view:

bluerooster_0-1661485014776.png

 

Greatly appreciate any insights on this please, thank you.

1 ACCEPTED 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 ) )

vzhangti_0-1662087925153.png

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.

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hello amit, 

 

Thank you for your reply. However, I did not get the desired result. The new column returned the following values:

UniqueWeek NumberOpening StockProposed ShipmentDemandOpening Stock After
G5231431120035120
G5231432120704285
G52314331204070113
G52314341204013083
G523143512080140-7
G523143612010040-67
G5231437120403660
X7832131200057200
X78321322002048143
X783213320090120115
X78321342006015085
X783213520060150-5
X78321362006049-95
X78321372006052-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:

UniqueWeek NumberOpening StockProposed ShipmentDemandOpening Stock After
G5231431120035120
G5231432120704285
G52314331204070113
G52314341204013083
G5231435120801400
G5231436120100400
G5231437120403660
X7832131200057200
X78321322002048143
X783213320090120115
X78321342006015085
X7832135200601500
X783213620060490
X7832137200605211

 

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

vzhangti_0-1662087925153.png

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.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.