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.
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?
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.
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"
))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.
Sure, I will do. I'll mark this as the solution and open a new thread if I need to.
Thanks again.
You are welcome.