Forum Discussion

swolfe2's avatar
swolfe2
Helper I
2 years ago

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!

Need Help

1 Reply

  • swolfe2's avatar
    swolfe2
    Helper I
    2 years ago

    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?