Hi, I'm trying to work out how much the storage cost would be over the next few weeks/months (based on a date range selected using a slicer) but seem to be getting mixed results with everything I've tried.
I have a fixed price for weekly storage cost per pallet. I'm trying to make a calculated column and/or measure that will take that fixed weely storage cost and calcuate the cost over the coming weeks/months based on what the user selects.
I have tried several different things like getting the number of weeks offset from my calendar table (based on last date selected in slicer return the number of weeks this is away from the first date) and then multiple the storage per week cost by that number, which should be correct but the result is not as expected. I end up with really high numbers.
Below is an example with the last column being the expected outcome.
| Data Slicer (Slider) | 07/01/2024 | 04/02/2024 | 4 Weeks |
| | | | |
| SKU | Pallet Quantity | Weekly Storage Cost | Based on Date Range Selected |
| Product 1 | 0.5 | £2.50 | £10.00 |
| Product 2 | 1 | £5 | £20 |
| Product 3 | 2 | £10 | £40 |
Any help would be much appreciated.
