Forum Discussion

sshekar7's avatar
sshekar7
Frequent Visitor
10 years ago
Solved

data formating results in 1905

I have two fields employees and Year. I am using this formula to calculate growth rate.

 

Growth % = divide (sum(Data[EMPLOYEES]) - calculate(sum(Data[EMPLOYEES]),SAMEPERIODLASTYEAR(Data[YEAR])), calculate(sum(Data[EMPLOYEES]),SAMEPERIODLASTYEAR(Data[YEAR])))

 

I am using sum because i have other fields which slices the employment data.

 

This gives error because Year is not date field. In "Modeling" tab, if i set the format to date then all the values become 1905. I understand its converting year 2013 to number which excel understands but is there is a easier way to fix this? 

 

I assume there has to be easier way to convert year values to date that PBI can use for calculations like this.

  • You can use the below method to convert your year to date.

     

    1. Convert the data type of your Year column to Tex
    2. Add custom Column to and use below formula to append date and montht to Year filed

    1. convert the new column type to date

     

    Hopefully this will help.

3 Replies

  • Habib's avatar
    Habib
    Icon for Continued Contributor rankContinued Contributor

    You can use the below method to convert your year to date.

     

    1. Convert the data type of your Year column to Tex
    2. Add custom Column to and use below formula to append date and montht to Year filed

    1. convert the new column type to date

     

    Hopefully this will help.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks for the Solution, but I have to use this to get the right solution:

      Date = "1/1/" & INT([Year])