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

Join 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.

Reply
mxfx
Frequent Visitor

query M: Use today if endate is empty

I have a function to calculate elapsed business days between two dates:

mxfx_0-1695753851022.png

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



1 ACCEPTED SOLUTION
JoeBarry
Solution Sage
Solution Sage

Hi @mxfx 

 

In Power Query, highlight the End Date column and in an empty cell right click and relace value. Enter a fake date.

JoeBarry_0-1695806732190.png

 

 

 

In the formula ribbon replace the fake date

JoeBarry_1-1695806773604.png

 

with 

 

Date.From(DateTime.LocalNow())

JoeBarry_2-1695806825978.png

Thanks

Joe

 

 

If this post helps, then please Accept it as the solution

 

View solution in original post

2 REPLIES 2
mxfx
Frequent Visitor

... don't know how I did not think of that thank you!!

JoeBarry
Solution Sage
Solution Sage

Hi @mxfx 

 

In Power Query, highlight the End Date column and in an empty cell right click and relace value. Enter a fake date.

JoeBarry_0-1695806732190.png

 

 

 

In the formula ribbon replace the fake date

JoeBarry_1-1695806773604.png

 

with 

 

Date.From(DateTime.LocalNow())

JoeBarry_2-1695806825978.png

Thanks

Joe

 

 

If this post helps, then please Accept it as the solution

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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