Forum Discussion

unknown917's avatar
unknown917
Icon for Helper IV rankHelper 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
Icon for Super User rankSuper 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
    Icon for Helper IV rankHelper IV
    1 year ago

    Akash_Varuna - Thank you for your quick response!  I am not terribly familiar with M Code.  Any chance to steer me in the right direction for the code necessary for last 7 days?  

  • unknown917's avatar
    unknown917
    Icon for Helper IV rankHelper 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
      Icon for Super User rankSuper 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
        Icon for Helper IV rankHelper 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...??