Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to extract data between two dates from sql server to Power Bi desktop . Which mode is suitable?

Hi,

 

My client wants two random date data from the SQL database and to load them to the Power Bi desktop. For example, the end user wants to know the sale between Jan 12- Jan14 . 

Kindly advise a solution for this?

 

 

Need Help
  • Seanan's avatar
    Seanan
    4 years ago

    Hi Anonymous 

    My SQL knowledge is still relatively simple so I unfortunately wouldn't be able to give you a confident answer on how to do this in a stored procedure.

    However, for the scenario above, one approach would be to load all of the data into PowerBI Desktop, then you can create a filter and apply that to all pages to show data from the previous day.

    Then you can add a slicer with your date range, so that a user can select the time frame of data that they want to see (See example below)

6 Replies

  • Seanan's avatar
    Seanan
    Solution Supplier
    4 years ago

    Hi Anonymous 

    There is 2 ways you can approach this.

    1. In SQL create a stored procedure that pulls back all of the data and in the WHERE clause you specify the date range. E.g WHERE [ColumnName].DateTime >= '12-1-2022' AND [ColumnName].DateTime <= '14-01-2022'. Then use this data in the report (This is the better option for performance)
    2. Pull all of the data into the report and then use a filter to display the data from that date range  

    I hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Seanan,

     

    Thanks for the advice.

    I want a dynamic date function because I'm not sure which date they are going to select. They are maintaining  2 years of data in the datawarehouse.

    Can we use direct query?

    • Seanan's avatar
      Seanan
      Solution Supplier
      4 years ago

      Anonymous 

      Will the dates ever change? For example today they want Jan12-Jan14 but in a month they would want to see data for Feb12-Feb14?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Seanan  Yes. I have to load every day data(Day-1) to Power BI. 

        There are 2 scenarios:

        1. when the stakeholder opens the Power bi mobile app, it should contain previous date data.

        eg: today is 27th June then the power bi dashboard should contain yesterday's data.

        2. If a user wants to check the sales data between two dates of the previous week,month,

        or last year. 

        Is there a way to add the above 2 scenarios in a single stored procedure?