Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Formula to calculate YoY

Hi!

 

I'm trying to create a measure to calculate YoY but I don't know how to do it... all the examples I've found use time functions, but I don't have a calendar table so I cannot use them.

 

I only have year results, so my Year column is set as number, not date, because if I set it as date it generates full dates that I don't have...

 

This is what I need, very simple calculation to do in excel, but I don't know how to recreate it in power bi...

 

 

I could do it manually for each year, but if my next data set have 20 years it's going to be a nightmare... I was trying to get a generic formula to calculate it. I've tried to calculate -1 on the year table and similar things, but either gives me errors or wrong results...

 

Can anyone help with this, please?

 

Thank you! 🙂

Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

     

    Aha, then try to plot a table visual with measure like this:

    YoY% =
    VAR _year = MAX(data[Year])
    VAR _sales = SUM(data[Sales])
    VAR _lastsales =  
    CALCULATE(
        SUM(data[Sales]),
        data[Year] = _year-1
    )
    RETURN
    DIVIDE(_sales - _lastsales, _lastsales  )
     

8 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

     

    not sure about your exact expectation, hope this helps:

    The code to the new column:

    YoY%2 = 
VAR _year = [Year]
VAR _lastsales =
MINX(
    FILTER(
        TableName,
        TableName[Year] = _year-1
    ),
    TableName[Sales]
)
RETURN
IF(
    _lastsales=BLANK(),
    BLANK(),
    DIVIDE([Sales]-_lastsales, _lastsales)
)

     

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      In general, DAX is good and easy for columns, but could be cubersome handling rows. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi FreemanZ and Mahesh0016 

       

      Thanks both for replying to me!

       

      FreemanZ what I see in your example (YoY%2) is exactly what I want! But I cannot make it work... 😥

       

      If I write "Year" like your example (VAR _year = [Year]),

      I get this error:

       

      "The value for 'Year' cannot be determined. Either the column doesn't exist, or there is no current row for this column."

       

      If I reference the table TableName[Year] then I get this error:

       

      "A single value for column 'Year' in table 'TableName' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

       

      I've tried to add "MAX" fuction to get the last year. I don't get an error but I don't have my result either...

       

      What do I need to do to be able to write it like you without the errors?

       

      Thank you! 😊

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        it seems you are writing a measure. The code is to add a calculated column.