Weekly data - can you still use Time Intelligence functions?

[Anonymous]

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?

[Ashish_Mathur]

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.

[Anonymous]

Hi, thanks for your reply.

Apologies, I perhaps wasn't clear enough in my original post. I do not have a date column, only Year and Week.

[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.

[Anonymous]

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.

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

Thanks a lot for this, it has worked and let me move forward!

One final question I have is how to you set up a rolling total through to the end of the year? I currently have the following measure which works but only totals figures to the current week:

Cumulative App Volume Target =
CALCULATE (
    SUM ( Targets[Online Target] )+sum(Targets[OBTM Target])+sum(Targets[Offline Target]),
FILTER(   
ALL( 'Targets'),
       'Targets'[WeekNo] <= MAX ('Targets'[WeekNo])&&Targets[Metric]="Volume"&&Targets[Type]="Apps"
       ))

[Ashish_Mathur]

You are welcome.   Please don't just type the formula.  Explain the question, show the data and also the expected result.  Share the link from where i can download the file.

[Anonymous]

Sure, I will do. I'll mark this as the solution and open a new thread if I need to.

Thanks again.

[Ashish_Mathur]

You are welcome.