Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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!
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
15 | |
13 | |
12 | |
11 |