Nikki Bueno de Mesquita Posted September 6, 2019 Share Posted September 6, 2019 I have a data table which has the aggregated values of a product with four rates attached to it. So each product has 4 rates with a start and end dateand average values for that time period (call this the aggregated view). I also have the raw data and want to visualize the data that has been selected in the aggregated view on the raw data. The aggregated data looks something like this: Product Rate Start End Average Visitors A 1 1/1/2019 1/6/2019 130 A 2 1/8/2019 1/13/2019 143 A 3 1/15/2019 1/20/2019 155 A 4 1/22/2019 1/27/2019 167 B 1 1/29/2019 2/3/2019 123 B 2 2/5/2019 2/10/2019 210 B 3 2/12/2019 2/17/2019 240 and the raw data looks something like this: Start Date Visitors 1/1/2019 100 1/2/2019 120 1/3/2019 160 1/4/2019 130 1/5/2019 145 1/6/2019 127 1/7/2019 88 1/8/2019 90 1/9/2019 145 1/10/2019 150 1/11/2019 130 1/12/2019 150 What I want to achieve: Start Date Visitors Product Dates Selected 1/1/2019 100 A 1 1/2/2019 120 A 1 1/3/2019 160 A 1 1/4/2019 130 A 1 1/5/2019 145 A 1 1/6/2019 127 A 1 1/7/2019 88 0 1/8/2019 90 0 1/9/2019 145 B 1 1/10/2019 150 B 1 1/11/2019 130 B 1 1/12/2019 150 B 1 I was thinking on adding a new column that says dates selected but I am struggling to get the aggregated dates to match up with the raw data. Can someone help me with this one Thanks Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted September 10, 2019 Share Posted September 10, 2019 You can make use of LastValidBefore function provided the data is in order with dates as you have mentioned above 1) Based on start date match from both tables, insert end date column in RawData table. 2) Then create calculated column using LastValidBefore function which will fill in the null values with till valid value is encountered LastValidBefore([End])3) Then create another calculated column for selected dates case when [start Date] Link to comment Share on other sites More sharing options...
Nikki Bueno de Mesquita Posted September 25, 2019 Author Share Posted September 25, 2019 Thanks this worked! Link to comment Share on other sites More sharing options...
Nikki Bueno de Mesquita Posted September 25, 2019 Author Share Posted September 25, 2019 Thanks this worked! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now