Forum Discussion

jesor's avatar
jesor
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago
Solved

Creating a visualization based on bins of years

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 m...
Need Help
Tutorial Requests
  • dtartaglia's avatar
    dtartaglia
    8 years ago

    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())

dtartaglia's avatar
dtartaglia
Icon for Resolver I rankResolver I
8 years ago

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's avatar
jesor
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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!!