Forum Discussion

gemcityzach's avatar
gemcityzach
Icon for Helper IV rankHelper 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"

     

gemcityzach's avatar
gemcityzach
Icon for Helper IV rankHelper IV
2 years ago

I like that 🙂 I'll build a test today and see how it works. Is there a way to bucket it for > 120 days so I can just lump everything that isnt 30/60/90/120 into that bucket?

DataInsights's avatar
DataInsights
Icon for Super User rankSuper 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
    Icon for Helper IV rankHelper 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
      Icon for Super User rankSuper 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"