Calculating due dates in the future intervals

[gemcityzach]

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 days, 90 days, or greater-than 90 days. However, what I'm seeing when using Date.InIsNextNDays is that an item can show up in 60, 90 or 120 days. It seems to me I need something like:

Effective Date is between X (60) and Y (90) or 90 and 120. What's the preferred method to do those operations?

[DataInsights]

@gemcityzach,

Try this custom column in Power Query:

each
  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 
	"NONE"

[gemcityzach]

What is the benefit of this over having multiple calculated Boolean-type columns for each period? I'm assuming that I can still do a count measure on that  column to get the count of items 'due30'?

ctDue30 = CALCULATE (

  COUNTROWS ( 'table' [Due Next N Days] )

  , [Due Next N Days ] = "dueNext30"

  ) --end countrows

) --end calculate

[DataInsights]

@gemcityzach,

Yes, you can create a count measure for due30, etc. If a row will have "Yes" for a maximum of one future interval, it's simpler to have one column with the future interval that is "Yes". Otherwise, you have to expand your table horizontally (additional columns) to handle each future interval. With the single-column approach, you can add that column to a visual and it will automatically slice the data. You could simplify your ctDue30 measure to just COUNTROWS('table') and the visual would apply the future interval filter to each cell in the matrix, for example.

Similarly, if you had a sales table and wanted to group each row into the appropriate sales band (0 - 99, 100 - 199, etc.), it would be more efficient to have a single column Sales Band. Otherwise, you would have to add a column for each sales band interval, and you wouldn't be able to select multiple sales bands in a slicer because they would be separate columns.

[gemcityzach]

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]

@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]

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 · Accepted solution]

@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"