Forum Discussion
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.
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:00 | 20 |
| 2/02/2019 0:00 | 30 |
| 3/02/2019 0:00 | 40 |
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-msftMicrosoft Employee
Hi Anonymous
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
Thanks Cherie for your reply. I am planning to have SSIS to do this job to create dataset and later consume in power BI.