Forum Discussion
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 ...
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)
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?
Arthur_NS With seasons do you have year column as well?
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)Arthur_NS I think with the seasons like this, it will not work in this way. We should have some other column also as differentiater since multiple year fall in a season.