Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JasonG-BI
Helper I
Helper I

Slicer to show dates between on stock report

Hi All,

 

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 belowImage for community.jpg

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @JasonG-BI,

 

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.

chart with start and end date

 

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.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you @v-shex-msft

 

But adding to the source data is not an option currently as generated live from a sales system.

 

I will keep trying 

 

Jason

JasonG-BI
Helper I
Helper I

Still struggling on this one be a massive help if anyone had any ideas? 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.