Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
8 | |
6 | |
5 | |
5 | |
5 |
User | Count |
---|---|
10 | |
8 | |
6 | |
6 | |
6 |