Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a function to calculate elapsed business days between two dates:
It works but if there is no EndDate, it returns an empty cell, I would like it to use "today" if there is no EndDate.
I tried creating a new parameter "Today" which would use the new column "Today" if no EndDate, I also tried with "DateTime. LocalNow" instead of a new parameter but both fail:
(StartDate as date, EndDate as date, Today as date, HolidayList as list) as number =>
let
DateList = List.Dates(StartDate, Number.From((if EndDate is null then Today else EndDate) - StartDate) , #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, List_Holidays),
CountDays = List.Count(RemoveHolidays)
in
CountDays
or
(StartDate as date, EndDate as date, Today as date, HolidayList as list) as number =>
let
DateList = List.Dates(StartDate, Number.From((if EndDate is null then DateTime.LocalNow else EndDate) - StartDate) , #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, List_Holidays),
CountDays = List.Count(RemoveHolidays)
in
CountDays
Solved! Go to Solution.
Hi @mxfx
In Power Query, highlight the End Date column and in an empty cell right click and relace value. Enter a fake date.
In the formula ribbon replace the fake date
with
Date.From(DateTime.LocalNow())
Thanks
Joe
If this post helps, then please Accept it as the solution
... don't know how I did not think of that thank you!!
Hi @mxfx
In Power Query, highlight the End Date column and in an empty cell right click and relace value. Enter a fake date.
In the formula ribbon replace the fake date
with
Date.From(DateTime.LocalNow())
Thanks
Joe
If this post helps, then please Accept it as the solution
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.