Forum Discussion

unknown917's avatar
unknown917
Helper IV
1 year ago
Solved

Incremental Refresh - building historical model week by week

Hello PI folks -    Beginning to read up on incremental refresh and not sure if my scenario applies.  I have a data model that needs to be refreshed say weekly but I need to retain the data from th...
Need Help
Tips and Tricks
Tutorial Requests
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi unknown917,

     

    If the parameters are not set up correctly, this issue will occur.
    Ensure that your RangeStart and RangeEnd parameters are correctly named (case-sensitive) and have the Date/Time data type, not just Date.
    If the issue persists, please share a screenshot of your parameter setup so we can identify any mistakes.

    FYI please refer below document,

    Configure incremental refresh for Power BI semantic models - Power BI | Microsoft Learn

     

    If this post helps, then please consider Accepting as the solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

     

Akash_Varuna's avatar
Akash_Varuna
Super User
1 year ago

Yes, it can be achieved using Incremental Refresh in Power BI.Try to do this

Create RangeStart and RangeEnd parameters in Power Query to filter your Date column dynamically (e.g., last 7 days). Configure Incremental Refresh to retain historical data (e.i in your case, 2 years) while refreshing only the latest week's data, and publish it to the Power BI Service.

unknown917's avatar
unknown917
Helper IV
1 year ago

I've tried a couple of things fumbling around and haven't cracked it.  Here's what I've tried so far:

 

Last Week Start Date = (Today()-WEEKDAY(TODAY())+1)-7

Last Week End Date = (Today()-WEEKDAY(TODAY())+1)-1

 

No syntax errors, but once filtered, my query yields no results.  Suggestions?

 

Much appreciated!

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Try this 
    let

    // Replace with your table

    Source = YourTable,

     

    // Define the RangeStart and RangeEnd parameters dynamically

    RangeStart = #datetime(), // Adjust the start date

    RangeEnd = #datetime(), // Adjust the end date

     

    // Filter rows based on the RangeStart and RangeEnd

    FilteredRows = Table.SelectRows(Source, each [Date] >= RangeStart and [Date] < RangeEnd)

    in

    FilteredRows

    • unknown917's avatar
      unknown917
      Helper IV
      1 year ago

      hello, 

       

      I have tried this, but when I attempt to set the incremental refresh, it says I haven't set up parameters yet...??

      • Akash_Varuna's avatar
        Akash_Varuna
        Super User
        1 year ago

        Ok , Could you set up parameters in power bi go to home tab manage parameters new parameters there you can set the start date and end date ie ranges then use those parameters