Dynamic Parameters for Incremental Refresh

[rutujakadam_11]

I have set up incremental refresh using Date/Time parametrs. I want the data to be refreshed on 1st of every current month to till date. While setting up the parameters for incremental refresh I have created 2 parameters- RangeStart & RangeEnd. RangeStart is defined  in a way that it should start on 1st date of month & RangeEnd to end on todate.  The details are Type:Date/Time, Suggested Value: Any value & Current Value: 11/1/2024 12:00:00 AM (RangeStart) & 11/28/2024 12:00:00 AM(RangeEnd). But can I set up these parameters dynamically, so as every month I won't have to set up the Current value manually. I want them to refresh the data from 1st of every current month to todate.

I have tried the Query option within parameter but that to requires a manual current Value, Also worked around the M-code to get the dates dynamically but the Incremental Refresh will only reference to Parameter and not the M-code so had to comment out that step. 
Parammeter

Data

Following is the M-code but the dynamic logic is commented-out-

let
// Load your source data
Source = Excel.Workbook(File.Contents("C:\Users\snehalkumar.kadam\Downloads\Incremental Refresh.xlsx"), null, true),
#"Incremental Refresh_Sheet" = Source{[Item="Incremental Refresh",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Incremental Refresh_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Name", type text}, {"City", type text}, {"LastModified", type datetime}}),

// Dynamically calculate RangeStart (1st of the current month)
//RangeStart = Date.StartOfMonth(DateTime.LocalNow()),

// Dynamically calculate RangeEnd (current date and time)
//RangeEnd = DateTime.LocalNow(),

// Filter rows based on LastModified column
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [LastModified] >= RangeStart and [LastModified] <= RangeEnd),

// Sort rows to get the latest record for each ID
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"ID", Order.Ascending}, {"LastModified", Order.Descending}}),

// Group rows by ID and keep the latest record
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"LatestRecord", each _, type table [ID=nullable number, Name=nullable text, City=nullable text, LastModified=nullable datetime]}}),
#"Expanded LatestRecord" = Table.ExpandTableColumn(#"Grouped Rows", "LatestRecord", {"Name", "City", "LastModified"}, {"LatestRecord.Name", "LatestRecord.City", "LatestRecord.LastModified"}),

// Rename columns for clarity
#"Renamed Columns" = Table.RenameColumns(#"Expanded LatestRecord",{{"LatestRecord.Name", "Name"}, {"LatestRecord.City", "City"}, {"LatestRecord.LastModified", "LastModified"}}),

// Remove duplicates by ID
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"ID"})
in
#"Removed Duplicates"

[FarhanJeelani]

Hi @rutujakadam_11 ,

To set up dynamic incremental refresh for Power BI:

1. Create Parameters: Define RangeStart and RangeEnd as Date/Time with dummy values (e.g., 11/1/2024 and 11/28/2024).

2. Use Dynamic Logic in Query: Update your M-code to calculate dynamic dates but still filter using the parameters for incremental refresh:

   CurrentDate = DateTime.LocalNow(),
   DynamicRangeStart = Date.StartOfMonth(CurrentDate),
   DynamicRangeEnd = CurrentDate,
   #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [LastModified] >= RangeStart and [LastModified] <= RangeEnd)

3. Set Up Incremental Refresh: In Power BI Desktop, configure RangeStart and RangeEnd for incremental refresh and publish the report.

Power BI Service will use your dynamic logic to fetch data from the 1st of the current month to today during refresh.

Please mark this as solution if it helps. Appreciate Kudos.