Forum Discussion

uk-roberto92's avatar
uk-roberto92
Frequent Visitor
1 month ago
Solved

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 fin...
Need Help
  • Lodha_Jaydeep's avatar
    Lodha_Jaydeep
    1 month ago

    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.

Lodha_Jaydeep's avatar
Lodha_Jaydeep
Solution Sage
1 month ago

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.