Forum Discussion

Saaam's avatar
Saaam
Frequent Visitor
2 years ago
Solved

Satisfy requirement from multiple warehouse

Hi, how can i achieve something like this?   These are my requirement and if i can satisfy them picking qty from different warehouse i should suggest that option!   Thanks
Need Help
  • Gabry's avatar
    Gabry
    2 years ago

    Ok so I think the measure should look like this:

     

    Result =
    VAR RequiredQty = 'Foglio1'[REQ QTY]
    VAR WH1_Stock = 'Foglio1'[WH1 stock]
    VAR WH2_Stock = 'Foglio1'[WH2 stock]
    VAR WH3_Stock = 'Foglio1'[WH3 stock]
    VAR WH4_Stock = 'Foglio1'[WH4 stock]

    VAR TakeFromWH1 = IF(WH1_Stock >= RequiredQty, RequiredQty, WH1_Stock)
    VAR RemainingAfterWH1 = RequiredQty - TakeFromWH1

    VAR TakeFromWH2 = IF(WH2_Stock >= RemainingAfterWH1, RemainingAfterWH1, WH2_Stock)
    VAR RemainingAfterWH2 = RemainingAfterWH1 - TakeFromWH2

    VAR TakeFromWH3 = IF(WH3_Stock >= RemainingAfterWH2, RemainingAfterWH2, WH3_Stock)
    VAR RemainingAfterWH3 = RemainingAfterWH2 - TakeFromWH3

    VAR TakeFromWH4 = IF(WH4_Stock >= RemainingAfterWH3, RemainingAfterWH3, WH4_Stock)
    VAR RemainingAfterWH4 = RemainingAfterWH3 - TakeFromWH4

    RETURN
    IF(TakeFromWH1>0, "Take " & TakeFromWH1 & " from WH1", IF(TakeFromWH1+TakeFromWH2+TakeFromWH3+TakeFromWH4>0,"Take ", " ") &
    IF(TakeFromWH2 > 0,   TakeFromWH2 & " from WH2", "") &
    IF(TakeFromWH3 > 0,   TakeFromWH3 & " from WH3", "") &
    IF(TakeFromWH4 > 0, TakeFromWH4 & " from WH4", "") &
    IF(RemainingAfterWH4 > 0, " order " & RemainingAfterWH4, "")
    )

    Don't forget to kudos and accept the solution 😉

     

     

Saaam's avatar
Saaam
Frequent Visitor
2 years ago

Hi, i'm looking for a dax measure!

The warehouse have priority order WH1 -> WH2 -> WH3 -> WH4

 

I'm tryng to do some cumulative sum but i don't know how to deal with the remainder stock 

 

The model is just like the image! Item (with rank for requirements priority) and each warehouse stock

 

Thanks

 

Gabry's avatar
Gabry
Icon for Super User rankSuper User
2 years ago

Ok so I think the measure should look like this:

 

Result =
VAR RequiredQty = 'Foglio1'[REQ QTY]
VAR WH1_Stock = 'Foglio1'[WH1 stock]
VAR WH2_Stock = 'Foglio1'[WH2 stock]
VAR WH3_Stock = 'Foglio1'[WH3 stock]
VAR WH4_Stock = 'Foglio1'[WH4 stock]

VAR TakeFromWH1 = IF(WH1_Stock >= RequiredQty, RequiredQty, WH1_Stock)
VAR RemainingAfterWH1 = RequiredQty - TakeFromWH1

VAR TakeFromWH2 = IF(WH2_Stock >= RemainingAfterWH1, RemainingAfterWH1, WH2_Stock)
VAR RemainingAfterWH2 = RemainingAfterWH1 - TakeFromWH2

VAR TakeFromWH3 = IF(WH3_Stock >= RemainingAfterWH2, RemainingAfterWH2, WH3_Stock)
VAR RemainingAfterWH3 = RemainingAfterWH2 - TakeFromWH3

VAR TakeFromWH4 = IF(WH4_Stock >= RemainingAfterWH3, RemainingAfterWH3, WH4_Stock)
VAR RemainingAfterWH4 = RemainingAfterWH3 - TakeFromWH4

RETURN
IF(TakeFromWH1>0, "Take " & TakeFromWH1 & " from WH1", IF(TakeFromWH1+TakeFromWH2+TakeFromWH3+TakeFromWH4>0,"Take ", " ") &
IF(TakeFromWH2 > 0,   TakeFromWH2 & " from WH2", "") &
IF(TakeFromWH3 > 0,   TakeFromWH3 & " from WH3", "") &
IF(TakeFromWH4 > 0, TakeFromWH4 & " from WH4", "") &
IF(RemainingAfterWH4 > 0, " order " & RemainingAfterWH4, "")
)

Don't forget to kudos and accept the solution 😉

 

 

  • Saaam's avatar
    Saaam
    Frequent Visitor
    2 years ago

    Thanks so much! It works perfectly!

    • Gabry's avatar
      Gabry
      Icon for Super User rankSuper User
      2 years ago

      Forgot to mention that it is a calculated column if you need a measure it needs some changes

      • Saaam's avatar
        Saaam
        Frequent Visitor
        2 years ago

        Yes i did it, thanks!