Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        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