Forum Discussion
Defininf RangeStart / RangeEnd via query
Hi,
is there a way to define parameters for incremental refresh via a Query?
I want to have something like
RangeStart = STARTOFMONTH(TODAY())
RangeEnd = ENDOFMONTH(TODAY())
But I can't find a way to set it up this way...
Hi uk-roberto92,
You can achive same with some of the steps blow.
1) Use the parameters with date values according to your incremental refresh date window and update the power query to read from those and set-up the incremental refresh for the tables you want.
set up the policy for same.
For Example,
#"Filtered Rows" = Table.SelectRows(Source,each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)2) Use the Dynamic parameter value so you don't need to go to parameter section on the service.RangeStart: Date.StartOfMonth(Date.From(DateTime.LocalNow()))
RangeEnd: Date.EndOfMonth(Date.From(DateTime.LocalNow()))
This option can overwrite by the refresh policy on the power bi service schedule refresh, As they are not alway the current month in production. So, you need to set up the expression accordingly.
I hope I am able to help you, please consdier as an accepted solution if helps or give some kudos.
5 Replies
Hi uk-roberto92,
You can achive same with some of the steps blow.
1) Use the parameters with date values according to your incremental refresh date window and update the power query to read from those and set-up the incremental refresh for the tables you want.
set up the policy for same.
For Example,
#"Filtered Rows" = Table.SelectRows(Source,each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)2) Use the Dynamic parameter value so you don't need to go to parameter section on the service.RangeStart: Date.StartOfMonth(Date.From(DateTime.LocalNow()))
RangeEnd: Date.EndOfMonth(Date.From(DateTime.LocalNow()))
This option can overwrite by the refresh policy on the power bi service schedule refresh, As they are not alway the current month in production. So, you need to set up the expression accordingly.
I hope I am able to help you, please consdier as an accepted solution if helps or give some kudos.
Hi uk-roberto92 - on the top of Lodha_Jaydeep solution, One additional point i would like to add is that Incremental Refresh only recognizes RangeStart and RangeEnd when they are Power Query (M) parameters.
After the dataset is published, the Power BI Service automatically supplies the appropriate RangeStart and RangeEnd values for each partition during refresh. The service does not simply refresh the current month unless your refresh policy is explicitly configured that way.Also, ensure the filter on the date column remains query-foldable.
If query folding is broken before the RangeStart/RangeEnd filter is applied, Incremental Refresh may not work as expected.
Hope it helps.
Hi uk-roberto92 ,
Thanks for reaching out to fabric community.
Could you review the suggestion provided by rajendraongole1 and Lodha_Jaydeep and let us know if you have any additional questions, we are happy to address.
Thanks!!
Hi,
RangeStart and RangeEnd used for Incremental Refresh must be Power Query parameters of type Date/Time. They can't be defined dynamically using another query or expressions like STARTOFMONTH(TODAY()) or ENDOFMONTH(TODAY()).
The good news is that you don't need to update these parameters manually in the Power BI Service. They are primarily used during development (for filtering data in Power BI Desktop). Once Incremental Refresh is published, the Power BI Service automatically manages the refresh window based on the policy you've configured (e.g., store the last 5 years, refresh the last 1 month).
If your goal is simply to refresh the current month's data, configure the Incremental Refresh policy accordingly rather than trying to make RangeStart and RangeEnd dynamic.
If you specifically need dynamic dates during development, you could create separate Power Query variables using Date.StartOfMonth(DateTime.LocalNow()) and Date.EndOfMonth(DateTime.LocalNow()), but these cannot replace the official RangeStart and RangeEnd parameters required by Incremental Refresh.
For more details, see the Microsoft documentation:
Could you clarify whether you're asking about:
Power BI Desktop (development/testing), or
Incremental Refresh after publishing to the Power BI Service?
If you found this reply helpful, please consider giving it a Kudos. If it helps resolve your question, marking it as the Accepted Solution will help other community members with the same scenario.
Hi uk-roberto92 ,
Could you please review the solution provided above and let us know if you have any further questions.
Thanks!!
