Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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

 

UniqueWeek NumberOpening StockProposed ShipmentDemandOpening Stock AfterLead Time (Week)
G52314CHA53385311200351201
G52314CHA53385321207042851
G52314CHA533853312040381131
G52314CHA533853412040371151
G52314CHA533853512040411181
G52314CHA533853612040401171
G52314CHA533853712040361171
G78321PLE51190312000572002
G78321PLE51190322000481432
G78321PLE51190332009045952
G78321PLE511903420060531402
G78321PLE511903520060511472
G78321PLE511903620060491562
G78321PLE511903720060521672

 

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

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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
    )

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      thank you so much Jihwan, it works wonderfully!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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