Forum Discussion
Filter Function Help
Might need a small pic of your model showing the relationships. Are the construction/transaction tables related by ID, with construction id a unique value?
transaction table:
ID transaction date time segment room number visitors revenue 4652 1/1/2010 Morning 1 1 $ 7.50 7854 1/2/2010 Afternoon 2 5 $ 37.50 9635 1/3/2010 Before Noon 3 4 $ 30.00 7845 1/4/2010 Morning 4 6 $ 45.00 construction table
ID Construction Start Construction End 4652 1/1/2010 3/15/2010 7854 8/5/2011 12/7/2011 9635 9/7/2011 1/1/2012
Date key table
Date Year Month Week Day 1/1/2010 2010 1 1 5 1/2/2010 2010 1 1 6 1/3/2010 2010 1 1 7 Tables are linked together by unique Building ID and I also linked everything to the Date table (in hopes of being able to manipulate views/cuts of data later).
Ya, you will have an easy time w/ filtering transactions by date, and filtering transactions by construction... but you have an issue w/ your calendar table relating to construction table... since there are 2 dates.
If you make a relationship between your calendar table and construction on Construct Start... then filtering to June 2010 is going to show ONLY construction that STARTED in June, which is... maybe not what you want?
Let's talk about a specific measure to make this easier? Like... you want... revenue year to date, broken out by construction?
I could make the construction 2 different tables. That would be an easy enough thing to do. What I'm trying to show is total revenue 12 months before construction starts, revenue 12 months after construction ends...and ideally any number of quarters, months etc. So the apples to apples comparison wouldn't be in individual months/years, but instead be based on number of days before or after construction started.
"1 year after renovations were completed, location 1 had x revenue, while location 2 had y revenue after it's remodel (that ended at a completely different time)."
"location 1 construction ended 1/1/2010, in 6 months it made $x. In the same 6 months before construction started location 1 made $y."....basically looking at the effectiveness of construction....
Thank you so much for the help!
