cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheBlacklist
Resolver I
Resolver I

Incremental Refresh - Future values

Hi all,

 

I've setup incremental refresh for the main FACT table in our dataset. This table includes actuals and budget data. 

 

THe incremental refresh is working correctly, however for the budget numbers it filters all the future values out of the dataset. For example: We have budget numbers for the entire year, when I setup the incremental refresh it only brings in values up to year to date. 

 

How do I change the settings for incremental refresh to include budget numbers for the full year? I see in the documentation about effective date but I don't where/how to apply this settings to the incremental refresh. 

 

Any help would be great. 

 

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#xmla-endpoint-be...

 

TheBlacklist_0-1600981202223.png

 

Thanks

 

1 ACCEPTED SOLUTION

hi @TheBlacklist 


Here is the extract below

GilbertQ_0-1601256797279.png

 

And what you would need to do is to change the effectiveDate to be the date in the future you want it to go into.

For example if you wanted it to go until the end of 2020 you would change it to the following:

 

{ 
  "refresh": {
    "type": "full",

    "applyRefreshPolicy": true,
    "effectiveDate": "12/31/2020",

    "objects": [
      {
        "database": "IR_AdventureWorks", 
        "table": "FactInternetSales" 
      }
    ]
  }
}

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

8 REPLIES 8
GilbertQ
Super User II
Super User II

Hi there

As far as I know with incremental refreshing it will only go as far as the current date and not into the future.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ thank you for the reply. In the documentation it talks about using effective date to pull future values out. Do you know how I can use this?

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#xmla-endpoint-be...

 

"effectiveDate – If an incremental refresh policy is being applied, it needs to know the current date to determine rolling window ranges for the historical range and the incremental range. The effectiveDate parameter allows you to override the current date. This is useful for testing, demos, and business scenarios where data is incrementally refreshed up to a date in the past or the future (for example, budgets in the future). The default value is the current date."

hi @TheBlacklist 


Here is the extract below

GilbertQ_0-1601256797279.png

 

And what you would need to do is to change the effectiveDate to be the date in the future you want it to go into.

For example if you wanted it to go until the end of 2020 you would change it to the following:

 

{ 
  "refresh": {
    "type": "full",

    "applyRefreshPolicy": true,
    "effectiveDate": "12/31/2020",

    "objects": [
      {
        "database": "IR_AdventureWorks", 
        "table": "FactInternetSales" 
      }
    ]
  }
}

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

Ah I should've read the documentation properly. I had to create a new script then copy and paste the effective code and execute.

 

Got it working. Thank you

Awesome thanks for letting us know!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi there

You are correct in that will apply if you are using Power BI Premium, where you can control it via the XMLA.

If it is in Power BI Pro I do not think that this is possible.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ  Yeah thats right. We have premium. Do you know how to apply this? I'm struggling to find where to apply the effective date

Hi, 

 

Did you find where to apply these changes?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.