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

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