Forum Discussion
Petanek333
Helper III
4 years agoCalculate 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...
- 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 _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
PaulDBrown
Community Champion
4 years agoCan you please provide sample data or a PBIX file?
- Petanek3334 years ago
Helper III
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