Forum Discussion

Arthur_NS's avatar
Arthur_NS
Helper I
4 years ago

Evolution with condition

Hi all,   I have a pretty straightforward table with 3 columns: Year | Number of sales | YOY   Basically what I would like to do is calculate the YoY evolution of sales. The only twist is that ...
Need Help
Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

Hi Arthur_NS ,

 

Below code would give your ideal output:-

Measure = 
var selected_year = int(SELECTEDVALUE(Sheet1[Year]))

var _current_year_num = CALCULATE(SUM(Sheet1[Sales]),Sheet1[YoY]="YOY" && int(Sheet1[Year]) = year(TODAY()))
var _current_year_deno = CALCULATE(SUM(Sheet1[Sales]),all(Sheet1[Year]), Sheet1[YoY]="YOY" && int(Sheet1[Year]) =year(TODAY())-1)
var _current_year = DIVIDE(_current_year_num,_current_year_deno)-1

var _other_year_num = CALCULATE(SUM(Sheet1[Sales]),int(Sheet1[Year]) = int(selected_year))
var _other_year_deno = CALCULATE(SUM(Sheet1[Sales]),int(Sheet1[Year]) = int(selected_year)-1)
var _other_year = DIVIDE(_other_year_num,_other_year_deno)-1

return IF(selected_year = YEAR(TODAY()),_current_year,_other_year)
  • Arthur_NS's avatar
    Arthur_NS
    Helper I
    4 years ago

    That works great thanks a lot!

    One last question, how would you handle the same situation but instead of Year you have a Season, so for example "2021/22", "2020/21" etc.
    I guess in this case the formula that includes TODAY() wouldn't work?

    • Samarth_18's avatar
      Samarth_18
      Community Champion
      4 years ago

      Arthur_NS With seasons do you have year column as well?

      • Arthur_NS's avatar
        Arthur_NS
        Helper I
        4 years ago

        Yes I can add it to the database, but for one season there can be 2 different years as shown below.
        Also, there might not be a 2022 in our database (latest year is 2021 in the table below)