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"

     

gemcityzach's avatar
gemcityzach
Helper IV
2 years ago

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's avatar
DataInsights
Super User
2 years ago

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's avatar
    gemcityzach
    Helper 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
      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")