Incremental load-odbc custom query-rolling two financial year

[rob_vander2]

Hi All,

I want to setup incremental load for storing rolling two financial year of data. Note that I am using ODBC connection to connect to source and using custom query. Financial year starts from April. Let's say if I start my first load on 31-Mar-2025, I should load data from 01-Apr-2023 to 31-Mar-2025. When I run next load, data should be loaded incrementally. Let's say if I run next load on 01-Apr-2025, only data for 01-Apr-2025 should be load incrementally, but overall data should start from 01-Apr-24 because now data for new financial year is loaded, hence data from 01-Apr-2023 to 31-Mar-2024 should be dropped from partition.

How can I setup such incremengtal load?

[v-tsaipranay]

Hi @rob_vander2 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @rob_vander2  ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hi @rob_vander2 ,

Thank you for reaching out to Microsoft fabric community forum and sharing your scenario.

To implement a rolling two-year financial window (April–March) with incremental refresh in Power Query via ODBC, the key is to create dynamic date parameters that calculate your financial year start and end dates based on the current date.

Use these parameters to filter your source data so only records within the desired financial window load during refresh. Then configure Power BI’s incremental refresh policy on that filtered data to append new data and remove records outside the rolling window.

Since Power BI’s incremental refresh is calendar-based by default, customizing the date logic in your parameters and query filtering is essential to align with your fiscal year. Make sure to validate your filters and refresh logic before deploying.

For more details on configuring incremental refresh and best practices, you can refer to Microsoft’s official docs here: Incremental refresh in Power BI.

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[rob_vander2]

@v-tsaipranay  Thanks for the reply. I am new to this apce. Could you guide me step by step approach? Is it possible to test incremental load from power bi desktop?

[v-tsaipranay]

Hi @rob_vander2 ,

Thanks for getting back. Yes, I’d be happy to guide you.

Since you're new to this space, here's a simple step-by-step approach to get started with incremental refresh:

• In Power BI Desktop, create two parameters: RangeStart and RangeEnd (type: Date/Time).
• In Power Query, filter your date column using these parameters include rows where your date is ≥ RangeStart and < RangeEnd.
• Load the data and right-click the table → choose "Incremental refresh".
• Define how many years of data to keep (e.g., last 2 years) and how much recent data to refresh (e.g., 1 month).
• Save and publish the dataset to Power BI Service incremental refresh works only in the Service, not fully in Desktop. Once published, schedule a refresh to test the behavior.

While you can't simulate full incremental behavior in Power BI Desktop, you can test the date filtering by manually adjusting the RangeStart and RangeEnd parameter values and previewing the filtered data.

You can also refer to Microsoft’s official guide for visuals and further explanation: Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[v-tsaipranay]

Hi @rob_vander2  ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[rob_vander2]

@v-tsaipranay  I am not able to implement this. I need step by step guide or PBIX file

[v-tsaipranay]

Hi @rob_vander2 ,

Please use the document provided below as a reference and try implementing incremental refresh: Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

Thank you.

[rob_vander2]

@v-tsaipranay  I am not able to find the scenario in this link I am working on.

[v-tsaipranay · Accepted solution]

Hi @rob_vander2 ,

I apologize for sharing the incorrect link earlier.

Please use the link below it walks you through the steps to set up incremental refresh and choose the archival period that works best for your needs: Configure incremental refresh for Power BI semantic models - Power BI | Microsoft Learn

Also, look at the attached screenshot. It shows where and how you can set the archival period based on your requirements.

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you for your patience and understanding.