the dates in this data are in number format so i can date back to previous year to calculate for say "% growth".
I tried using DateAdd() of BI and Parralleldate() but the dates are not in the right format.
Please who can help me sort out this problem
I have got the years and number of internet users for each country. Trying to calculate for a "% growth" of the internet users i need to date back to previous year using dateadd() or parralleldate() functions of BI.
To solve this, I went back to the original doc at Excel and formatted the dates from TEXT format to DATES "yyyy" format. Uploaded the new DOC and yet unable to get the desired result.
BI formular looks
Calculate(sumx( [USA],USA[no.internet users],
Paralleldate(USA[Year],-1, YEAR))
For date functions (dateadd, Parallelperiod, Previousyear) to work you will need a Calender table in your data model.
As you only have a year field, add a calculated column to create a date field like Date = DATE('USA'[Year],1,1) and then create a Calender table using Calendar = CALENDAR(FIRSTDATE('USA'[Date]),LASTDATE('USA'[Date])) and create a relationship between both the tables.
Now you can use CALCULATE([Sum], PARALLELPERIOD(Cal[Date],-1,YEAR)) to get Previous year numbers.