Creating a visualization based on bins of years

[jesor]

I have a sharepoint list tracking data, this field is a date field but for my purpouses I only need the year.  

I need to be able to do a calculation based on this single column which will give me different bins where I can create a bar chart but have it dynamic by using the year(today()) function to do math against, thus having a flexible model when the reports are refreshed.

Does anyone know how to get my bin's defined?  I can't figure out what DAX functions to write, or if i need a column vs a measure to create what would be a filter based on the year(today()) as the starting point.

It makes sense in english but I just can't figure it out in DAX, such as for the between 1 and 3 years.  If [date initiated] is from today, or 3 years ago, give me a count.  And so on for the other bins.  Please ignore the N/A portion, I can just gather that count from the null values in my data.

I assume I need to have a measure or column per bar in the bar graph.

Thanks,

Jesse

[dtartaglia · Accepted solution]

You could create a calculated column ans SWITCH() similar to this:

Evaluation = SWITCH(
    TRUE(),
    YEAR(TODAY()) = Sheet1[Dates].[Year] || YEAR(TODAY()) < Sheet1[Dates].[Year] + 3, "Between 1 and 3 Years",
    YEAR(TODAY()) = Sheet1[Dates].[Year] || YEAR(TODAY()) > Sheet1[Dates].[Year] + 2 && YEAR(TODAY()) < Sheet1[Dates].[Year] + 6, "Between 4 and 6 Years",
    YEAR(TODAY()) = Sheet1[Dates].[Year] || YEAR(TODAY()) > Sheet1[Dates].[Year] + 5, "Greater Than 6 Years",
    BLANK())

[jesor]

Thanks for the help!! This looks like exactly what I need.  My last attempt was with a lof of conditional IF statements in a column, but using this simplifys the approach and is easier to read.  Thanks so much!!

[Anonymous]

I'd suggest a date table.  This would let you deciede, on a date by date basis, which bin a give date falls into.  You wouldn't have to hand create anything, just set some rules up on how your bins can be decieded. 

For example, you might go by calandar year, you so you do a formula of nested ifs that take the row's Year value and compares it to the current dates year value.  That result puts it in the correct bucket.