Forum Discussion
Weekly data - can you still use Time Intelligence functions?
Hi,
If the week number is 2, then 2-1=1. 1*7=7. & days added to the first day of the first week would be first day of the second week. This logic continues.
For 2 years, try this calculated column formula
=IF(YEAR(Data[Week Number])=2017,DATE(2017,1,1)+7*(Data[Week Number]-1),DATE(2018,1,1)+7*(Data[Week Number]-1))
Hope this helps.
Super UserHi,
Your question was very clear - it is me who misunderstood it. Sorry about that. If we have data only for one year and we also know the first date of the first week, then we can generate a date column.
Yes, I have the year and week number for every record. It runs from a calendar year, so weeks 1 thorugh to 52.
How would you suggest I do this?
- Ashish_Mathur
Hi,
Try this calculated column formula
=DATE(2018,1,1)+7*(Data[Week Number]-1)
If the first week starts from January 3, 2018, then modeify the formula to
=DATE(2018,1,3)+7*(Data[Week Number]-1)
Hope this helps.
Thanks for that, it does help. Although I am unsure how this bit works?
*(Financials[WeekNo]-1)
Also, if I have 2017 and 2018 how would I amend the formula to cater for this?
- Ashish_Mathur
Hi,
If the week number is 2, then 2-1=1. 1*7=7. & days added to the first day of the first week would be first day of the second week. This logic continues.
For 2 years, try this calculated column formula
=IF(YEAR(Data[Week Number])=2017,DATE(2017,1,1)+7*(Data[Week Number]-1),DATE(2018,1,1)+7*(Data[Week Number]-1))
Hope this helps.