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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors