Forum Discussion

Petanek333's avatar
Petanek333
Icon for Helper III rankHelper III
4 years ago
Solved

Calculate the value closest to selected date range

Hi, to simplify my question, I have a table like this. The column Quantity represents the change of stock on given date and column Current stock represents the value I want to find.  and a st...
  • PaulDBrown's avatar
    4 years ago

     See if this works for you.

    (I've added a Dimension for Warehouse to the model)

    Measure for the stock at min date selected:

    Stock at Min Selection =
    VAR _MinSel =
        MIN ( 'Calendar'[Dates] )
    VAR _Stock =
        LASTNONBLANKVALUE (
            FILTER ( ALL ( 'Calendar'[Dates] ), 'Calendar'[Dates] <= _MinSel ),
            [Sum Stock]
        )
    RETURN
        _Stock
    

     Measure for the stock at max date selected:

    Stock at Max Selection =
    VAR _MaxSel =
        MAX ( 'Calendar'[Dates] )
    VAR _Stock =
        LASTNONBLANKVALUE (
            FILTER ( ALL ( 'Calendar'[Dates] ), 'Calendar'[Dates] <= _MaxSel ),
            [Sum Stock]
        )
    RETURN
        _Stock
    

     

     

    I've attached the sample PBIX file