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 😉

     

     

7 Replies

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

    Hello,

    sure you can achive that. What's your issue on this? Are you looking for dax formula or something else?

    Do you have a model on Powerbi? Please provide more information.

    • 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 😉