Forum Discussion
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 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?
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"
7 Replies
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"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
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.
