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
Arthur_NS's avatar
Arthur_NS
Helper I
4 years ago

Sorry but I still get the same

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Arthur_NS is it possible for you to share PBIX file after removing sensitive data?

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