Forum Discussion

hammerman's avatar
hammerman
Frequent Visitor
4 years ago
Solved

Opening Stock For Multiple Products

Hi Power BIers,

I've got a stock adjustment table that I'd like to use to calculate opening stock level. It's across multiple products and dates. 

 

I'd essentially like to fill down the opening balance by date until a new opening balance is entered. But then I'd also like to sum across products. Here's what I'm after. 

 

 

Here's my data table in Excel: 

 

And here's what I'd like the output to look like in Power BI: 

 

Note that I have a date table and a product control table. The formula needs to use these 2 tables to calculate the final result through this table, the stock adjustment table. 

 

I hope that makes sense. Thanks for your help!

 

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, hammerman 

     

    You can manually modify the value of total. Use SELECTEDVALUE ( table[column]1 ) = BLANK ().

    Like this:

    measure =
IF (
    SELECTEDVALUE ( Date[Date] ) = BLANK (),
    CALCULATE ( SUM ( Table[Inventory Value] ) ),//calculate total sum
    CALCULATE (
        LASTNONBLANKVALUE ( Date[Date], SUM ( Table[Inventory Value] ) ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
)

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

3 Replies

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

    hammerman , Prefer to use a Date table, this will ensure all works onlt for date

     

    calculate(lastnonblankvalue(Date[Date], Sum(Table[Inventory Value])), filter(all('Date'), 'Date'[Date] <= max( 'Date'[Date] )))

  • hammerman's avatar
    hammerman
    Frequent Visitor
    4 years ago

    Thanks amitchandak although unfortunately that hasn't worked. I'd like it to sum up the total across each product for each date from the inventory table at the total level. This formula just gives me the last non blank.

     

    I've got a date table and a product control table in my model. I'm thinking it might be something like for each date SUMX the last non blank?  

     

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, hammerman 

       

      You can manually modify the value of total. Use SELECTEDVALUE ( table[column]1 ) = BLANK ().

      Like this:

      measure =
IF (
    SELECTEDVALUE ( Date[Date] ) = BLANK (),
    CALCULATE ( SUM ( Table[Inventory Value] ) ),//calculate total sum
    CALCULATE (
        LASTNONBLANKVALUE ( Date[Date], SUM ( Table[Inventory Value] ) ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
)

      Did I answer your question? Please mark my reply as solution. Thank you very much.
      If not, please feel free to ask me.

      Best Regards,
      Community Support Team _ Janey