Forum Discussion
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.
- Convert the data type of your Year column to Tex
- Add custom Column to and use below formula to append date and montht to Year filed
- convert the new column type to date
Hopefully this will help.
3 Replies
- Habib
Continued Contributor
You can use the below method to convert your year to date.
- Convert the data type of your Year column to Tex
- Add custom Column to and use below formula to append date and montht to Year filed
- convert the new column type to date
Hopefully this will help.
- sshekar7Frequent Visitor
Thanks Habib.. it worked..
- AnonymousNot applicable
Thanks for the Solution, but I have to use this to get the right solution:
Date = "1/1/" & INT([Year])