Forum Discussion
Helper IIICalculate the value closest to selected date range
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 _StockMeasure 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 _StockI've attached the sample PBIX file
Helper IIIHi 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?
Community ChampionYes, 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
- PaulDBrown
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
- Petanek333
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.
- Petanek333
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 🙂
- PaulDBrown
Yes, you need to include all the fields over which the index reference is to be calculated in the ALLEXCEPT expression