Calculate the value closest to selected date range

I have the utmost respect for you PaulDBrown , works exactly like it is supposed to. Thank you!

See if these new measures work:

Totals at MIN =
SUMX (
    SUMMARIZE ( StockTable, Products[Products], 'Dim Warehouse'[dWarehouse] ),
    [INDEX Stock at Min Selection]
)

Totals at MAX =
SUMX (
    SUMMARIZE ( StockTable, Products[Products], 'Dim Warehouse'[dWarehouse] ),
    [INDEX Stock at Max Selection]
)

Well this is turning out to be quite a challenge. Sorry about the "measure testing"...

I think (HOPE!) this structure will sort things out and provide final measures...

Totals at MIN =
SUMX (
    VALUES ( 'Dim Warehouse'[dWarehouse] ),
    SUMX ( Products, [INDEX Stock at Min Selection] )
)

Totals at MAX =
SUMX (
    VALUES ( 'Dim Warehouse'[dWarehouse] ),
    SUMX ( Products, [INDEX Stock at Max Selection] )
)

Nested SUMX are not a good idea, but I haven't been able to find another working solution unfortunately.

New file attached

PaulDBrown  thank you so much for your help in the previous days. Works as it is supposed to!

I don't want to steal your time anymore, but is there an article or something about how this trick with index column works? Your code is obviously great but I don't understand it 🙂

No problem. See if I can clarify the method. I am assuming that the dataset is constructed chronologically and that the integrity is kept on import. This means that each row moves forward chronologically. When you add an index column, it reflects the chronological nature (so row 3 is "newer" than row 2 which is "newer" than row 1...So if you then have more than 1 row for any particular date, the highest index will be the more recent set of data. Make sense?

As for the measure...

INDEX Stock at Min Selection =
VAR _MinSel =
    MIN ( 'Calendar'[Dates] )
VAR _MXIndex =
    CALCULATE (
        MAX ( StockTable[Index] ),
        FILTER (
            ALL ( 'Calendar'[Dates] ),
            'Calendar'[Dates] <= _MinSel
                && NOT ( ISBLANK ( [Sum Stock] ) )
        ),
        ALLEXCEPT ( StockTable, Products[Products], 'Dim Warehouse'[dWarehouse] )
    )
RETURN
    CALCULATE (
        [Sum Stock],
        FILTER ( ALL ( StockTable ), StockTable[Index] = _MXIndex )
    )

We need the MAX index value for each Warehouse/Product before or on the minimum (in this example) date selected.

The first filter creates a table fo dates which are less than or equal to the minimum date selected. (you need the ALL to remove the filter in the selection, or you would not get dates before the selection).

The second filter with ALLEXCEPT removes the filters on the StockTable except for rows affected by the columns specified in the expression (so Warehouse and Product are kept). You can read about how this expression works in this video by the gurus at sqlbi.com

The final code in the RETURN  produces the sum of the stock value for the rows where the index is equal to the calculated max index by warehouse and product.

To get the sum of values, you need to calculate the stock value by product and then sum these values up. This is where SUMX comes in. SUMX iterates over the product table calculating the stock value by product and then sums these values up.

A shot video on SUMX:

Hope that helps!

Hi PaulDBrown , 

I hope I don't bother you with my questions already. Since the time you helped me I implemented those solutions and learned a lot from it.

However, when using this model I have run into a problem. In the sample file and also in the picture you posted, there is 563 pcs total stock at max date in B2B warehouse and 181 pcs in B2C warehouse. That is perfectly fine, but I did not find the way to show total stock across both warehouses. Is it possible without completely rewriting the code get a total value too?

For example if I have a simple table showing just products no matter the warehouse. 

Value for all the products should be 744 pcs

OR

Product A01 ... 163 pcs

Product B01 ... 400 pcs

Product C01 ... 181 pcs

I hope this filter context is not completely lost, I don't want to bother you with rewriting the code, just a little tweak would be great.

Hi PaulDBrown , it's me again 🙄.

I found an error that contradicts the logic of the report. New measures work great for the purpose of showing total stock across both warehouses. However, I found out that the min value (start date) of the date slicer affects the max value (end date) in some cases. 

Max value though should be independent of the min value date slicer, because it should be showing the stock value on the selected max date no matter the start date. But it is not.

I added new measures in this sample file and the total stock value on the end date is almost always 744 pcs, which is right. But if I move the start date closer to the end date, the end date starts to get affected. 

For example if the start date is 16.8.2022 (with end date being always 20.8.2022), then the end date stock value decreases to 445 pcs. 

I get that it has something to do with using summarize, but I just don't know how to remove the start date parameter out of the measure calculation.

Is it even possible to fix this and still use the SUMMARIZE function?

New problem sample file 

Yes, I see what you mean. The measure is not actually returning the Index needed, since it appears that the ALL() is negating the subsequent date reference. My apologies since I should have spotted that!

I've tested the following to calculate the following which return the correct stock values at max and min date references:

INDEX Stock at Min Selection =
VAR _MinSel =
    MIN ( 'Calendar'[Dates] )
VAR _MXIndex =
    CALCULATE (
        MAX ( StockTable[Index] ),
        FILTER ( ALL ( StockTable[Date] ), StockTable[Date] <= _MinSel ),
        ALLEXCEPT ( StockTable, Products[Products], 'Dim Warehouse'[dWarehouse] )
    )
RETURN
    CALCULATE (
        [Sum Stock],
        FILTER ( ALL ( StockTable ), StockTable[Index] = _MXIndex )
    )

INDEX Stock at Max Selection =
VAR _MinSel =
    CALCULATE ( MAX ( 'Calendar'[Dates] ), ALLSELECTED ( 'Calendar'[Dates] ) )
VAR _MXIndex =
    CALCULATE (
        MAX ( 'StockTable'[Index] ),
        FILTER ( ALL ( StockTable[Date] ), StockTable[Date] <= _MinSel ),
        ALLEXCEPT ( StockTable, Products[Products], 'Dim Warehouse'[dWarehouse] )
    )
RETURN
    CALCULATE (
        [Sum Stock],
        FILTER ( ALL ( StockTable ), StockTable[Index] = _MXIndex )
    )

Keeping the final SUMX measures as you have them (just beware that the correct totals are with SUMX(SUMMARIZE())...)

New file attached

I can see how the numbers are now correct (total sum is 736 instead of 744) but there is still one bug. If I choose the start date - end date like the first picture (27.7.2022 - 20.8.2022), values are start date = 732 and end date = 736.

But if I move the start date close to end date, the end date value (max value) changes to 

end date = 445.

Since the end date value should always be the same (the value of stock on 20.8.2022) no matter the start date, it is still not working.

In other words, no matter the min date selected in the date slicer, the max date value should always be the same. I am simply asking a question: What was the stock value on 20.8.2022 regardless of what start date I choose?

Does my question make sense? I hope you understand

Should I maybe use two separate date slicers, one for the min value and one for the max value? I just want to define two days (two point in time) that I want to compare without any interest for what happened "between" those two days.

Paul, please don't apologize for anything, you have been an incredible help. This does exactly what I want.

However in my original model with data I cannot share (that is why I created a simplified model) there are 4 other subcategories of Product. For Example product A01 is Accessories, Product B is Bikes etc. The previous measures worked for the subcategories as well so I did not mention it. But with these last measures it seems like I lost a filter context for anything else than Product and Warehouse.  

I added a subcategory Type into the model like this:

and the filter context is lost:

I am really sorry I keep adding to the problem, but I as mentioned these are the first measures that do not work for the subcategories. Only work for product and warehouse. 

Sample file with added dimension 

EDIT: Oh, seems like adding those dimensions (subcategories) to the ALLEXCEPT statement of the index measures solves the problem. I don't want to waste your time when not necessary, so can you just please confirm that this is OK? 

I really respect your time but I got this idea just right after posting the original reply 🙂 

Yes, you need to include all the fields over which the index reference is to be calculated in the ALLEXCEPT expression