Evolution with condition

[Arthur_NS]

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 when the Year selected is 2022, then only rows with "Yes" in column YOY should be considered.

Any idea how to construct this DAX formula?

Thanks a lot,

Arthur

[Arthur_NS]

Sorry but I still get the same

[Samarth_18]

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

[Arthur_NS]

Hello,

The pbix file can be found here: https://drive.google.com/file/d/14ff94knR1CG59264cK6_B9oZBSl1Ini3/view?usp=sharing

Also a little explanation with my table, and the calculation I'm trying to get at

Thank you

[Samarth_18]

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]

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]

@Arthur_NS With seasons do you have year column as well?

[Arthur_NS]

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)

[v-yalanwu-msft]

---

@Arthur_NS wrote:

---

I think it is ok. For the table you provided, could you provide the result display you want to output? In order to understand your logic and requirements more clearly.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samarth_18]

@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.

[Arthur_NS]

Would you have an alternative solution for the Season scenario?
Thanks a lot

[Arthur_NS]

Thanks but I get this

[Samarth_18]

@Arthur_NS try this:-

measure =
VAR selected_year =
    SELECTEDVALUE ( table[year] )
RETURN
    CALCULATE (
        SUM ( table[number of sales] ),
        FILTER ( table, int(table[year]) = int(selected_year) - 1 && table[YOY] = "Yes" )
    )

[Samarth_18]

Hi @Arthur_NS ,

Please try this:-

measure =
VAR selected_year =
    SELECTEDVALUE ( table[year] )
RETURN
    CALCULATE (
        SUM ( table[number of sales] ),
        FILTER ( table, table[year] = selected_year - 1 && table[YOY] = "Yes" )
    )

Thanks,

Samarth