Forum Discussion

gemcityzach's avatar
gemcityzach
Helper IV
2 years ago
Solved

Calculating due dates in the future intervals

I have a table with a number of fields including created date, issued date, effective date, closed date, etc. I want to create calculated columns to determine if the effective date is in 30 days, 60 ...
Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    gemcityzach,

     

    Try checking for null first:

     

    each
  if [Effective_Date] is null then
	null
  else if [Effective_Date] < DateTime.Date(DateTime.LocalNow()) then
	"NONE"
  else if Date.IsInNextNDays([Effective_Date], 30) then
	"dueNext30"
  else if Date.IsInNextNDays([Effective_Date], 60) then
	"dueNext60"
  else if Date.IsInNextNDays([Effective_Date], 90) then
	"dueNext90"
  else if Date.IsInNextNDays([Effective_Date], 120) then
	"dueNext120"
  else 
	"due>120"

     

DataInsights's avatar
DataInsights
Super User
2 years ago

gemcityzach,

 

Try this. It returns "NONE" if Effective_Date is less than today, and "due>120" in the ELSE clause.

 

each
  if [Effective_Date] < DateTime.Date(DateTime.LocalNow()) then
	"NONE"
  else if Date.IsInNextNDays([Effective_Date], 30) then
	"dueNext30"
  else if Date.IsInNextNDays([Effective_Date], 60) then
	"dueNext60"
  else if Date.IsInNextNDays([Effective_Date], 90) then
	"dueNext90"
  else if Date.IsInNextNDays([Effective_Date], 120) then
	"dueNext120"
  else 
	"due>120"

 

gemcityzach's avatar
gemcityzach
Helper IV
2 years ago

I'm getting all kinds of errors, particularly for dates that are now in the past or even in the near future (90 days out). Any thoughts on resolving that?

 

= Table.AddColumn(#"Changed Type", "dueNext30", each if Date.IsInNextNDays([Effective_Date], 30) & [Effective_Date] <> "null" then
"dueNext30"
else if Date.IsInNextNDays([Effective_Date], 60) & [Effective_Date] <> "null" then
"dueNext60"
else if Date.IsInNextNDays([Effective_Date], 90) & [Effective_Date] <> "null" then
"dueNext90"
else if Date.IsInNextNDays([Effective_Date], 120) & [Effective_Date] <> "null" then
"dueNext120"
else
"NONE")

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    gemcityzach,

     

    Try checking for null first:

     

    each
  if [Effective_Date] is null then
	null
  else if [Effective_Date] < DateTime.Date(DateTime.LocalNow()) then
	"NONE"
  else if Date.IsInNextNDays([Effective_Date], 30) then
	"dueNext30"
  else if Date.IsInNextNDays([Effective_Date], 60) then
	"dueNext60"
  else if Date.IsInNextNDays([Effective_Date], 90) then
	"dueNext90"
  else if Date.IsInNextNDays([Effective_Date], 120) then
	"dueNext120"
  else 
	"due>120"