Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

Incremental Refresh: Using a function to get the StartDate parameter

Hello All,

I'm wanting to implement incremental refresh on one of the fact tables that I have in the data model. I would like to use a function that dynamically calculates the X business datetime value from Y months in the future/past.

For example, if I were to pass in 11/15/2023, 8, -2 into the function, then the output would be 9/12/2023 as it was the 8th business day from 2 months ago. 

If I were to pass in 11/15/2023, 5, 1 into the function, the output would be 12/7/2023 as it is the 5th business day in the next month.

I would also need the output from that function to be used as a Parameter that is in datetime format.

Any help/advice would be greatly appreciated!

Frequent Visitor

After some tinkering, I was able to successfully come up with a function that provides the correct output. 


  func = 
      fn_Workday = (inputDate as date, inputMonthOffset as number, inputBusinessDayOffset as number) =>
          //Get the month offset value from inputMonthOffset
          DateWithMonthOffset = Date.AddMonths(inputDate, inputMonthOffset), 
          //Get the first day of the month from the inputMonthOffset
          FirstDayOfMonth = Date.StartOfMonth(DateWithMonthOffset), 
          //Get a list of all Monday - Friday dates
          BusinessDays = List.Select(
              #duration(1, 0, 0, 0)
            each Date.DayOfWeek(_) <> Day.Saturday and Date.DayOfWeek(_) <> Day.Sunday
          //Ensure the list is sorted in ascending order
          SortedBusinessDays = List.Sort(BusinessDays), 
          //Get the index 0 based position from the inputBusinessDayOffset
          BusinessDayPosition = SortedBusinessDays{inputBusinessDayOffset - 1}, 
          //Get the date/time value of the BusinessDayPosition
          BusinessDayPositionWithDateTime = DateTime.From(BusinessDayPosition)
  documentation = [
    Documentation.Name = " WORKDAY ", 
      = "This function will return the business day position from the month offset based on a reference date.", 
      = "This function will return the business day position from the month offset based on a reference date.", 
    Documentation.Category = " Nth Business Day ", 
    Documentation.Version = " 1.0 ", 
    Documentation.Author = " Author Name ", 
    Documentation.Examples = {
        Description = "  ", 
        Code        = " fn_Workday(#date(2023, 11, 15), 0, 8)", 
        Result      = " 11/10/2023 12:00:00 AM"
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))


How can I now use this function to dynamically set the RangeStart and RangeEnd parameter values based on a query? 




Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors