Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Cells To Filter Fact Table Via Start and End Date

Hi,   Can someone help me wherein I created a table namely Reporting Start and Reporting End and if I enter the desired day/month/year on any of the cells then the fact table will be filtered?   ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks Again...

 

The refreshed of query will only happen when I'm logged in the company's intranet and it didn't allow the use of Powerbi so if the report and start date can be done via excel, power query or sql  then the refreshed might happen

v-hashadapu's avatar
v-hashadapu
Community Support
1 year ago

Hi Anonymous , thank you for reaching out to the Microsoft Fabric Community Forum.

You can use Power Query and Excel formulas to dynamically filter your data based on the Reporting Start and End Dates entered by the user. Here's how you can do it:

  1. In an Excel sheet, create a small table with two columns as Reporting Start Date and Reporting End Date. Name this table tbl_ReportDates.
  2. Go to Data -> Get Data -> From Other Sources -> From SQL Server (or other source, depending on your database). Load the Fact Table into Power Query.
  3. Open Power Query Editor -> Home -> Manage Parameters -> New Parameter:
    Name: paramStartDate

Type: Date/Time

Value: =Excel.CurrentWorkbook(){[Name="tbl_ReportDates"]}[Content]{0}[Start Date]

  1. Repeat the same for paramEndDate, linking it to the End Date column.
  2. Apply a filter to your Fact Table in Power Query:
    [FactTable].[Date] >= paramStartDate and [FactTable].[Date] <= paramEndDate
  3. Click Close & Load to return the filtered table to Excel.
  4. Right-click the query in the Queries & Connections pane and select Properties.Check Refresh data when opening the file or manually refresh by clicking Data -> Refresh All.

Using SQL Query in Excel (Directly from Database)

If your data is in SQL Server, you can filter it dynamically using Excel's SQL query function.

  1. Go to Data -> Get Data -> From Database -> From SQL Server Database. Enter server name and database name. Click Advanced Options and enter a parameterized SQL query: SELECT * FROM FactTable

WHERE Date >= (SELECT StartDate FROM tbl_ReportDates)

AND Date <= (SELECT EndDate FROM tbl_ReportDates)

2. Load -> Table. Now, whenever you change the Start and End Dates in Excel, refresh the query via Data -> Refresh All.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.