Forum Discussion
Helper IIICalculate 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 standard calendar table - 'Calendar'[Dates] with unique dates.
I need an outcome like this:
The logic is that the formula has to find the nearest date lower or equal to the min and max range selection, so in this case dates 1.7.2022 and 19.8.2022. There are obviously many other IDs and Warehouses involved as well as the 'StockTable'[Date] has duplicate values. The best I could do is something like this for the upper part of the range, but it does not work. Can you please help me?
Latest date stock =
var selectedmaxdate = MAX('Calendar'[Dates])
return
CALCULATE(
SUM(StockTable[Current stock]),
'Calendar'[Dates] <= selectedmaxdate,
LASTDATE('Calendar'[Dates])
)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
20 Replies
PaulDBrownCommunity Champion
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
Petanek333Hi PaulDBrown , thank you very much for this. It works great, however it does not reflect one thing. As I mentioned in the original post, the StockTable[Date] column has duplicates even for the same IDs and Warehouses meaning more than one stock movement for one product in a day is possible.
See for example 21.6.2022 for the product B01. Correct value to be shown is the last stock movement = 173 pcs. I did not include times in the sample file so we cannot say what the last movement is. I did not think of it.
However, if there was time included in the Date column, would it show the last value in that day only and not the sum of all the values with selected date?
And is there any way to show only one value of Current stock if there are more records in one day without a time value?
For example as you can see below, there are three records on June 21st, 2022, but the measure would take only one (it does not matter which one) and show 173 pcs (or 170 or 199, really doesn't matter, but not the sum of it)
- PaulDBrown
Does your original data contain a Date/time field then?
- PaulDBrown
Can you please provide sample data or a PBIX file?
- Petanek333
Yes of course, thank you for participating in this thread.
Here is the file: Sample file
Correct numbers would be, lets say for product A01:
stock at 2.8.2022 = 95 for B2B warehouse and 75 for B2C
stock at 20.8.2022 = 100 for B2B warehouse and 63 for B2C