cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mohan_Babu Regular Visitor
Regular Visitor

Retrieve results set based on user input date. if date not available pick the most recent one.

Hi, 

 

I have a requirement to receive a single date or date range as a input from the user and filter my record set to display in report.  Some thing similar to how we handle parameters in SSRS reports. But i came to know we dont have such support in power BI service. 

I am able to retrieve results for a given date range but the problem is for some specific dates, stock details are not captured and in that case i need to populate the stocks for the previous available date. 

 

Please advice how it can be achieved. 

1 ACCEPTED SOLUTION

Accepted Solutions
Mohan_Babu Regular Visitor
Regular Visitor

Re: Retrieve results set based on user input date. if date not available pick the most recent one.

Thanks Cherie for your reply.  I am planning to have SSIS to do this job to create dataset and later consume in power BI. 

View solution in original post

5 REPLIES 5
Mohan_Babu Regular Visitor
Regular Visitor

Re: Retrieve results set based on user input date. if date not available pick the most recent one.

For below sample data, if the user inputted date is 04/02/2019 in the service then I should get stock value as 40 i.e stock as of 03/02/2019 because there is no stock details available for 04/02/2019. 

 

Date Stock
1/02/2019 0:0020
2/02/2019 0:0030
3/02/2019 0:0040
Mohan_Babu Regular Visitor
Regular Visitor

Re: Retrieve results set based on user input date. if date not available pick the most recent one.

Can i get a pseudo steps for how this can be done. 

 

I am thinking of doing the following. 

1. Have a date table and compare the stock date in stock table with date in data table.  

2. for missing dates in stock table, create a calculated  column to capture the stock count from previous available date. 

3. in the service, when the user selects a date, get a the stock details if its available from stock table, if not populate from the calculated column derived in step 2. 

 

Please let me know if above approach is correct. 

v-cherch-msft Super Contributor
Super Contributor

Re: Retrieve results set based on user input date. if date not available pick the most recent one.

Hi @Mohan_Babu 

 

Correct.I would suggest you use measure to get the dynamic date from columns when user selects a date.Here is the post for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Mohan_Babu Regular Visitor
Regular Visitor

Re: Retrieve results set based on user input date. if date not available pick the most recent one.

Thanks Cherie for your reply.  I am planning to have SSIS to do this job to create dataset and later consume in power BI. 

View solution in original post

v-cherch-msft Super Contributor
Super Contributor

Re: Retrieve results set based on user input date. if date not available pick the most recent one.

Hi @Mohan_Babu 

 

Got it, if you have resolved the issue, please mark the right reply as answer, and welcome to share your own solution. More people will benefit from here.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 57 members 1,241 guests
Please welcome our newest community members: