DAX Formula - if date field is null, use end of next year

[leatherhen99]

Hi All,
I have a start date and an end date... and I'm using a custom column in power query to give me the networkdays:

= Table.AddColumn(#"Removed Columns2", "Actual_Number_of_Days", each Networkdays([StartDate],[DueDate],HolList))

This is working as designed... until the DueDate is null... and that's because someone might not know the end date of their project... I'd like to set it up so that if there's not an end date, it will calculate the date through the end of the next calendar year... if not next year, then this year...

I am trying to update the [DueDate] within the custom column to an if statement, but DAX is new to me... so I'm struggling with updating this formula or creating a new column and updating it...
I have tried:

= Table.AddColumn(#"Removed Columns2", "Actual_Number_of_Days", each Networkdays([StartDate], if([DueDate] = Blank(), Date.EndOfYear, [DueDate]),HolList))

but it says there's an error, but it won't tell me what is wrong or how to fix it

and I've tried to create a custom field, but the null dates are still giving the errors

Any help would be appreciated!

 

[GraH]

Is Networkdays not a custom function? as far I know standard PQ functions have a signature like Date.Something.
So if you are going to add arguments that are not foreseen in that function it can't work.
When using if there needs to be a then and an else.
You should add this in the custom function to deal with all scenarios for dates being null. That look like the best solution.

or you can adjust your if statement

Table.AddColumn(#"Removed Columns2", "Actual_Number_of_Days", each
 if [DueDate] = null
    then Networkdays([StartDate], Date.EndOfYear(Date.From( DateTime.LocalNow() ) ) ,HolList)
 else Networkdays([StartDate], [DueDate] ,HolList ) )

FYI: this is an M issue (Power Query), not DAX (Power Pivot), so might want to update the title of your thread.

 

[leatherhen99]

Is Networkdays not a custom function? as far I know standard PQ functions have a signature like Date.Something.
So if you are going to add arguments that are not foreseen in that function it can't work.
When using if there needs to be a then and an else.
You should add this in the custom function to deal with all scenarios for dates being null. That look like the best solution.

or you can adjust your if statement

Table.AddColumn(#"Removed Columns2", "Actual_Number_of_Days", each
 if [DueDate] = null
    then Networkdays([StartDate], Date.EndOfYear(Date.From( DateTime.LocalNow() ) ) ,HolList)
 else Networkdays([StartDate], [DueDate] ,HolList ) )

FYI: this is an M issue (Power Query), not DAX (Power Pivot), so might want to update the title of your thread.

Thank you! This worked!
Yes, this is a custom networkdays function.
I'll be glad to change the title (for someone else to find...but I'm not sure how to edit the title... Thanks again!

 

[GraH]

Cool, glad I could help and you we're able to apply to your need. Thanks for the feedback and for having marked as solution.

 

[jdellasala]

I would LOVE to see someone actually post a Power Query function Networkdays! I don't find it anywhere. How does that code work?

 

[leatherhen99]

I would LOVE to see someone actually post a Power Query function Networkdays! I don't find it anywhere. How does that code work?

Here's what i found to get the function:

 

[leatherhen99]

so, I found an inconsistency, and started doing some research... 20 minutes into the video that's posted above, his code shows this:

= (StartDate as date, CompletionDate as date, HolList as list) as number =>
let
DateList = List.Dates(StartDate,Number.From(CompletionDate-StartDate)+1,#duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_,Day.Monday) <=5),
RemoveHolidays = List.RemoveItems(RemoveWeekends,HolList),
CountDays = List.Count(RemoveHolidays)
in
CountDays

My totals weren't adding up correctly, so I found another video... and the "RemoveWeekends" line is different, and my manually calculated networkdays now matches... apparently it needs to be <5... not <=5... so the code should look like this...
(

)

= (StartDate as date, CompletionDate as date, HolList as list) as number =>
let
DateList = List.Dates(StartDate,Number.From(CompletionDate-StartDate)+1,#duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_,Day.Monday) <5),
RemoveHolidays = List.RemoveItems(RemoveWeekends,HolList),
CountDays = List.Count(RemoveHolidays)
in
CountDays

 

[jdellasala]

so, I found an inconsistency, and started doing some research... 20 minutes into the video that's posted above, his code shows this:

= (StartDate as date, CompletionDate as date, HolList as list) as number =>
let
DateList = List.Dates(StartDate,Number.From(CompletionDate-StartDate)+1,#duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_,Day.Monday) <=5),
RemoveHolidays = List.RemoveItems(RemoveWeekends,HolList),
CountDays = List.Count(RemoveHolidays)
in
CountDays

My totals weren't adding up correctly, so I found another video... and the "RemoveWeekends" line is different, and my manually calculated networkdays now matches... apparently it needs to be <5... not <=5... so the code should look like this...
(

)

= (StartDate as date, CompletionDate as date, HolList as list) as number =>
let
DateList = List.Dates(StartDate,Number.From(CompletionDate-StartDate)+1,#duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_,Day.Monday) <5),
RemoveHolidays = List.RemoveItems(RemoveWeekends,HolList),
CountDays = List.Count(RemoveHolidays)
in
CountDays

Funny, when I pulled up the first video, this was the next in the queue. Good stuff! Thanks.