Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Year-to-Year formula

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

Formula
Missing data
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Easy but efficient solution! Thank you very much