Need an Expression to Split a DateRange column into 3 bins

Question

I want to represent count of ID's wrt to Daterange column. I tried to split it into bins but there was option to split into no of parts and not as 3 differnt date values.I want to split the Date Range column into 3 parts so that i can represent the data in a bar chart as Current,Pat and Future data. Each of the 3 bins are represented as:

1.Current - Count of ID for Current month[Dec 2016]. The data for current month should be dunamically calculated since if the next month comes the data should point to that by the dynalic calculation

2.Past - Count of ID for Data less than current month[Data < Dec 2016]. I need to be able to dynamically change the no of months using custom expression so that user can chnage th e no of months it goes back to. Need the expression in such a way that it can be set by a custom expression ,if not the nos can be changed at the expression

3.Future - Count of ID for Data greater than current month[Data Dec 2016]. I need to be able to dynamically change the no of months using custom expression so that user can chnage th e no of months in the future. There will be future dates available since it is a data for manintenance done in the future time.

This 3 data needs to be as a custom/binned column so that the data is represented as shown in the attached picture.

S3S S3S · Accepted Answer · 2016-12-06T17:55:17

You just need to create a calculated column...

case 
    when DatePart('month',[DateColumn]) = DatePart('month',DateTimeNow())  and DatePart('year',[DateColumn]) = DatePart('year',DateTimeNow()) then "Current"
    when [DateColumn] < DateTimeNow() and [DateColumn] >= DateAdd('month',${NumOfMonthsBack} * (-1),[DateColumn]) then "Past"
    when [DateColumn] > DateTimeNow() and [DateColumn] <= DateAdd('month',${NumOfMonthsAhead},[DateColumn]) then "Future"
end as [MonthRange]

Need an Expression to Split a DateRange column into 3 bins

1 Answers