Forum Discussion
Weekly data - can you still use Time Intelligence functions?
The lowest level of my current dataset is by week so I am not able to join it to a Date table in atypical way.
I need to be able to add a lot of time functions such as TotalYTD and Running totals.
How can you use the time functions if you don't have daily data?
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.
11 Replies
- Ashish_Mathur
Super User
Hi,
The Data/Time Intelligence functions should still work very well. Create a Calendar table with running dates and establish a connection from the Date column in your source data table to the Date column in your Calendar Table. You should then be able to use Date/Time Intelligence functions without a problem.
You may create a Calendar Table by using the following formnula under Modelling > New Table
=CALENDAR(MIN(Data[Dates]),MAX(Data[Dates]))
Hope this helps.
- Ashish_Mathur
Hi,
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.