I am trying to produce a stock report that not only shows todays stock but has a slicer so that we can see stock levels on days in the past. (i am working in direct query).
I have a table which show the stock status i.e in stock. Invoiced, Archived. This table has a status start date and Status end date. So for example if the status is "in stock" any date between those two dates the item is in stock. I also have a calendar table called general date built in to the the database.
I want to be able to select a date on the slicer and show all the items that would have been in stock on that day. I have tried using datesbetween but i am struggling with this one.
Any help on a measure would be much appreicated.
my tables are below
In my opinion, I will expand detail date between start date and end date to mapping records to each day's to help create daily stock statue visualizations.
As you said, you are works on directquery mode, so I think my solution not suitable for your scenario.
Maybe you can try to add new table with expand detail dates in datasource side.