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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
swolfe2
Helper I
Helper I

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!

1 REPLY 1
swolfe2
Helper I
Helper I

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

 

let
  func = 
    let
      fn_Workday = (inputDate as date, inputMonthOffset as number, inputBusinessDayOffset as number) =>
        let
          //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(
            List.Dates(
              FirstDayOfMonth, 
              Date.DaysInMonth(DateWithMonthOffset), 
              #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)
        in
          BusinessDayPositionWithDateTime
    in
      fn_Workday, 
  documentation = [
    Documentation.Name = " WORKDAY ", 
    Documentation.Description
      = "This function will return the business day position from the month offset based on a reference date.", 
    Documentation.LongDescription
      = "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"
      ]
    }
  ]
in
  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

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.