Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 budg...
Need Help
  • GilbertQ's avatar
    GilbertQ
    5 years ago

    hi Anonymous 


    Here is the extract below

     

    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" 
      }
    ]
  }
}

     

GilbertQ's avatar
GilbertQ
Super User
5 years ago
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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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-benefits-for-incremental-refresh

     

    "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."

    • GilbertQ's avatar
      GilbertQ
      Super User
      5 years ago
      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.
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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

    • GilbertQ's avatar
      GilbertQ
      Super User
      5 years ago

      hi Anonymous 


      Here is the extract below

       

      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" 
      }
    ]
  }
}

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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