Nut sure of it's supports your final solution requested but find my approach as reference below and stored in attached workbook version 9.3
a. TV GRP 0: zn([TV GRP])
b. Formula 2: SUM([TV GRP 0])+(PREVIOUS_VALUE(0)/1.43)
ISNULL.twbx 51.8 KB
But there is an issue using 3 different data sources connected ( relationship).
I still trying to understand what is going on.
Thank you for your help!
Can you share your workbook?
Unfortunately no, its a project for a customer.
But let me explain and maybe you can give me some insights.
1-I'm using a Google Analytics live connection and 2 excel files.
2-First, I created a calculated field in all my 3 sources to provide a Date+Hour dimension using this calculation DATEADD('hour',[Hour],[Date] ).
3-Second, I created a relationship between them using this new dimension Date+Hour.
I noted that when crossing Radio and Google Analytics trying to see a relation between Radio GRP and Sessions using the Date+Hour field of Radio source, the GRP looks OK but several gaps appear for the measure SESSIONS. Its the same for the other way, when I use my Date+Hour field of the Google Analytics source, the Sessions looks OK but several gaps appear for the Radio GRP.
My conclusion is that both sources don't contain all Date+Hour necessary to relate all data.
I'm planning to create a file from the scratch only containing all date+hour for the entire year and after that, connect the 3 sources in this new file.
What do you think?
Thank you for your support!