Forum Discussion

perpor's avatar
perpor
Icon for Helper V rankHelper V
8 years ago

Current year vs prevous year

Hello

 

I have a dataset with the following 2 columns, daily data.

I am trying to add a new measure with the var of each day over the day in the previous year

 

many thanks

Need Help

18 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    You new measure could be:

    Get Difference =

            var indexPY = CALCULATE(SUM([Index]),DATEADD([data],-1,YEAR))

            return ([index] - [indexPY] 

     

    where [index] and [data] the columns of your data set. The [data] column should of 'Date' or 'Date/Time' type

    • perpor's avatar
      perpor
      Icon for Helper V rankHelper V
      8 years ago

      thanks a lot

       

      but I have always the error:  The syntax for 'YEAR' is incorrect, also trying with lowercase 'year'

       

      maybe the [data] column is not seen correctly ? it comes from a sql server table defined as 'date' ...

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Do you have dates that are not appropriate? Try sorting your column to see min and max values. There might be something not correct there. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi perpor,

     

    You can try to use below formula(calculated column) to find out the previous year data. It will return blank if can't find the specific index value.

     

    Previou = LOOKUPVALUE('Table'[Index],'Table'[Date],DATE(YEAR([Date])-1,MONTH([Date]),DAY([Date])))

     

    Regards,

    Xiaoxin Sheng

     

    • perpor's avatar
      perpor
      Icon for Helper V rankHelper V
      8 years ago

      thanks a lot, I have now 'sintax error' on MONTH and I trying to understand why, change the format, eccc..

      • perpor's avatar
        perpor
        Icon for Helper V rankHelper V
        8 years ago

        solved, with my local setting I must use ';' instead of ','.

        But now I have the error:  'A table of multiple values was supplied where a single value was expexted' ...