Year-to-Year formula

[Anonymous]

Hello everybody,

I have a slight problem for my Year-to-Year formula. I want to calculate the evolution of sales from 2020 to 2021.

It goes as follows:

- Conso Net Sales = SUM('DATA (2)'[CONSO NET SALES1])

- ConsoNetSales PY (Previous Year) = CALCULATE([Conso Net Sales],CALCULATETABLE(FILTER('DATA (2)','DATA (2)'[CONSO NET SALES1]),DATEADD('Calendar'[Date], -1,YEAR)))

- ConsoNetSales YOY (Year-to-Year) =

VAR ValueCurrentPeriod = [Conso Net Sales]

VAR ValuePreviousPeriod = [ConsoNetSales PY]

VAR Result =

IF(NOT ISBLANK(ValueCurrentPeriod) && NOT ISBLANK(ValuePreviousPeriod), ValueCurrentPeriod - ValuePreviousPeriod)

RETURN

Result

So everything works. The only problem is that for some of my clients, I don't have data for 2020 and therefore, it does not want to show up in my Waterfall graph.

I know also the solution: I just have to indicate PowerBI that if there is no Data for 2020 (Previous Year), it just has to substract the data of 2021 with 0.

But I don't know how to add that in YOY formula...

Can you please help me?

Thank you very much

[amitchandak · Accepted solution]

@Anonymous , based on what I got

ConsoNetSales YOY (Year-to-Year) =
VAR ValueCurrentPeriod = [Conso Net Sales]
VAR ValuePreviousPeriod = [ConsoNetSales PY]+0
VAR Result =
ValueCurrentPeriod - ValuePreviousPeriod
RETURN
Result

[Anonymous]

Easy but efficient solution! Thank you very much