Cells To Filter Fact Table Via Start and End Date

[tksnota]

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?

Condition: Reporting Start Date will be 3 previous years of beginning of the current year / Reporting End Date will be 2 future years of the end of the current year

Thanks...tksnota

[v-hashadapu]

Hi @tksnota, Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @tksnota, Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @tksnota , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details. Thank you.

[dufoq3 · Accepted solution]

Hi @tksnota, check attached file.

Output

[v-hashadapu]

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

To achieve this, you can use DAX (Data Analysis Expressions) in Power BI to create dynamic date filters based on your Reporting Start and Reporting End dates.

1. create two measures for the start and end dates in your Power BI model.
2. You can create a calculated column or a measure to use these measures to filter your fact table.
   
   Please see the screenshots and the attached pbix file for your reference.

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.

[tksnota]

Thanks...I need the report to submitted via excel with the capability of doing the refreshed query based on the reported start and end entered by the user which can be 3 years ago or 2 years in the future with months being variable.

Will that be possible in Power BI and convert it to excel?

Thanks...tksnota...

[v-hashadapu]

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

1. Once you have the filtered table in Power BI, you can export it to Excel by using the "Export Data" feature.
2. In Excel, you can set up a query that connects to the Power BI dataset. To do this, go to the Data -> Get Data"-> From Power BI.
3. Sign in to your Power BI account if you aren't already.
4. You'll see a list of available datasets. Select the dataset that contains your filtered data.
5. Click "Load" to import the data into Excel. This will create a query that dynamically pulls data from your Power BI dataset.
6. You can set the query to refresh automatically or manually based on your needs. To do this, right-click on the query in the "Queries & Connections" pane and select "Properties."
7. In the query properties, you can set the refresh options, such as refreshing the data when the file is opened or at specific intervals.
8. Any changes in the Reporting Start Date and Reporting End Date will dynamically refresh the data in Excel.
   

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.

[tksnota]

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]

Hi @tksnota , 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]

4. Repeat the same for paramEndDate, linking it to the End Date column.
5. Apply a filter to your Fact Table in Power Query:
   [FactTable].[Date] >= paramStartDate and [FactTable].[Date] <= paramEndDate
6. Click Close & Load to return the filtered table to Excel.
7. 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.

[tksnota]

Hi,

I tried the filter and click 2024 but it doesn't changed the invoice date as shown.

Thanks...tksknota...

[nickyslothouwer]

Looks like you are using Excel. Have you tried using this option? 

Sorry my settings are set to dutch so you need to match the icon's based on your language in Excel.