DAX: Create Column/Measure that returns value based on other columns

[Anonymous]

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 G52314CHA53385 Week 34 Opening Stock After -> 113-38+40 = 115). 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. Lastly, the Opening Stock After should never be a negative value (e.g. if value is negative, return 0 instead).

Unique	Week Number	Opening Stock	Proposed Shipment	Demand	Opening Stock After	Lead Time (Week)
G52314CHA53385	31	120	0	35	120	1
G52314CHA53385	32	120	70	42	85	1
G52314CHA53385	33	120	40	38	113	1
G52314CHA53385	34	120	40	37	115	1
G52314CHA53385	35	120	40	41	118	1
G52314CHA53385	36	120	40	40	117	1
G52314CHA53385	37	120	40	36	117	1
G78321PLE51190	31	200	0	57	200	2
G78321PLE51190	32	200	0	48	143	2
G78321PLE51190	33	200	90	45	95	2
G78321PLE51190	34	200	60	53	140	2
G78321PLE51190	35	200	60	51	147	2
G78321PLE51190	36	200	60	49	156	2
G78321PLE51190	37	200	60	52	167	2

Would greatly appreciate some insights into this please, thank you!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Opening Stock after CC =
VAR _startweeknumber =
    MINX (
        FILTER ( Data, Data[Unique] = EARLIER ( Data[Unique] ) ),
        Data[Week Number]
    )
VAR _openingstock =
    MAXX (
        FILTER (
            Data,
            Data[Unique] = EARLIER ( Data[Unique] )
                && Data[Week Number] = _startweeknumber
        ),
        Data[Opening Stock]
    )
VAR _currentproposedshipmentcumulate =
    SUMX (
        FILTER (
            Data,
            Data[Unique] = EARLIER ( Data[Unique] )
                && Data[Week Number] < EARLIER ( Data[Week Number] )
        ),
        Data[Proposed Shipment]
    )
VAR _previousdemandcumulate =
    SUMX (
        FILTER (
            Data,
            Data[Unique] = EARLIER ( Data[Unique] )
                && Data[Week Number] < EARLIER ( Data[Week Number] )
        ),
        Data[Demand]
    )
RETURN
    IF (
        Data[Week Number] = _startweeknumber,
        _openingstock,
        _openingstock + _currentproposedshipmentcumulate - _previousdemandcumulate
    )

[Anonymous]

hello @Jihwan_Kim i encountered a small issue where i want the Opening Stock after CC to return 0 if the calculation returns a negative value. In calculating the next week's Opening Stock After CC it should use 0 instead of a negative value (previous week's Opening Stock After CC).

Currently, the formula uses the previous week's Opening Stock After CC value even if it's a negative value to calculate the current week's Opening Stock After CC.

Thank you once again for the help so far, very much appreicated!!

[Anonymous]

thank you so much Jihwan, it works wonderfully!