Incremental Refresh with SQL database

[vikramkumawat]

Hello,
I am trying to implement incremental refresh for a report created by SQL database.
i have followed the steps bellow:

• get data using import mode
• created RangeStart and RangeEnd Parameters with type date/time.
• filtered fact table's datetime column like : [DATE]  > RangeStart and [DATE] <=RangeEnd .
• query folding is enabled for fact table.
• defined refresh policy as shown in image
  

Now when i have published it and refreshed the sementic model I am getting data only for year 2021, 2022 and for year 2024 i am getting feb,march and april data . I was expecting data of Jan-2018 to march-2024.

I just need help to verify is there anything left in configuring incremental refresh.

or is my data is missing due to incremental refresh policies ? 
Thanks!

[lbendlin]

which policies did you decide on?  six years of storage and three months of refreshes?  Please show that setup.

Note:  Usually RangeStart is inclusive and RangeEnd is exclusive.

[vikramkumawat]

Hi @lbendlin,
Thanks for notifying me the usual way of filtering RangeStart and RangeEnd.

yes I applied policy as 6 years storage and 3 months refresh as bellow :

If the issue is because of setting RangeStart exclusive and RangeEnd inclusive,can you please give me a bit info about how these parameters works after publishing to the service so that I do not repeat mistakes like this.

Thanks!

[lbendlin · Accepted solution]

You can get the deep details here Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

Basic takeaway is that the RangeEnd of one partition must abut but not overlap the RangeStart of the next partition.