locked
year over year variance RRS feed

  • Question

  • Hello. how do i create a year over year formula that doesn't show values for the year that has no year before it in the dateable? Thanks. 
    Sunday, October 2, 2016 10:33 AM

Answers

  • First, calculate the previous year results with SAMEPERIODLASTYEAR. Then, before calculating the year over year percentage, check whether the previous year result is blank using the ISBLANK function.
    • Proposed as answer by Michael Amadi Tuesday, October 4, 2016 8:41 PM
    • Marked as answer by Charlie Liao Wednesday, October 19, 2016 5:38 AM
    Monday, October 3, 2016 7:52 AM
    Answerer
  • Hi Alex,

    In your scenario, you can create a calculated column by using the expression below
    =IF(ISBLANK(year(DATEADD('Date 1'[CalendarDate],-1,YEAR))),"NO","Yes")

    And then use this new created column as the slicer.


    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Tuesday, October 4, 2016 8:41 PM
    • Marked as answer by Charlie Liao Wednesday, October 19, 2016 5:38 AM
    Tuesday, October 4, 2016 6:50 AM

All replies

  • First, calculate the previous year results with SAMEPERIODLASTYEAR. Then, before calculating the year over year percentage, check whether the previous year result is blank using the ISBLANK function.
    • Proposed as answer by Michael Amadi Tuesday, October 4, 2016 8:41 PM
    • Marked as answer by Charlie Liao Wednesday, October 19, 2016 5:38 AM
    Monday, October 3, 2016 7:52 AM
    Answerer
  • Hi Alex,

    In your scenario, you can create a calculated column by using the expression below
    =IF(ISBLANK(year(DATEADD('Date 1'[CalendarDate],-1,YEAR))),"NO","Yes")

    And then use this new created column as the slicer.


    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Tuesday, October 4, 2016 8:41 PM
    • Marked as answer by Charlie Liao Wednesday, October 19, 2016 5:38 AM
    Tuesday, October 4, 2016 6:50 AM