Forum Discussion
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?
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
Hi Anonymous, check attached file.
Output
11 Replies
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.
Hi Anonymous , 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.
- create two measures for the start and end dates in your Power BI model.
- 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.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...
Hi Anonymous , thank you for reaching out to the Microsoft Fabric Community Forum.
- Once you have the filtered table in Power BI, you can export it to Excel by using the "Export Data" feature.
- 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.
- Sign in to your Power BI account if you aren't already.
- You'll see a list of available datasets. Select the dataset that contains your filtered data.
- Click "Load" to import the data into Excel. This will create a query that dynamically pulls data from your Power BI dataset.
- 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."
- In the query properties, you can set the refresh options, such as refreshing the data when the file is opened or at specific intervals.
- 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.
Hi Anonymous, check attached file.
Output
Hi Anonymous , 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.
Hi Anonymous, 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.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.
