Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Creating a column that returns a value based on a Year Slicer

I have a slicer in my dashboard that filters by the Year (see first screenshot below)

 

I have created an IF formula in the column '2019' to see that if the year is 2019, it will pick up the amounts in the "Actual" column. 

 

Is there a way to link the slicer i have created in my dashboard and incorporate it into my column '2019' formula such that my column will automatically generate?

 

For example, if I changed my Year Slicer to 2017, could my "2019" column pick up Actuals amount for 2017?

 

In addition, how could I link my "2018" column to the Year Slicer but for a year prior?

 

 

 

Need Help
Tips and Tricks

1 Reply

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    "For example, if I changed my Year Slicer to 2017, could my "2019" column pick up Actuals amount for 2017?"

    When you add the [Actual] column into the table and set like below, you will get sum data. 

     

    "In addition, how could I link my "2018" column to the Year Slicer but for a year prior?"

    Do like this:

     

    //column
Last amount = 
CALCULATE(
    SUM([Actual]),
    FILTER(
        Sheet1,
        [Year] = EARLIER([Year]) -1
    )
)

     

     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.