If you are unable to hit upon the solution by yourself, then posting a sample packaged workbook will help us help you.
The easiest way is manage this logic in formula.
if ([Site]="A" and [Date]>=date(#2016-06-01#))
or ([Site]="B" and [Date]>=date(#2016-06-15#))
or ([Site]="C" and [Date]>=date(#2016-08-27#))
If you have many sites and want to handle in different table, here is a
Make relationships only on [Site], not on [Date]
All the data should be aggretaged in this case.
if attr([Date])>= [Date Tbl 2] then sum([Goal]) - SUM([Table2].[Value]) else sum([Goal]) end
Goal Stretched_SM_9.3.twbx 91.6 KB
The data I am working with looks exactly like what is shown in the table.
I want to plot the cumulative savings over a time axis for all sites combined knowing that each site saves 100kWh/day starting on the start date in the table.
So how it should look in the above example is that we would see a line start at the 100kWh level on 06/01/2016...it would go up linearly (100kWh/day) until it hit 06/15/2016, then start going up faster by 200kWh/day...until it hit 08/27/2016 then go up 300kWh/day onwards...and so on for any other sites done during the year, until the end of the year.
It would look like this:
This would effectively give us a graphical representation of the cumulative savings for all the sites done in the year and see the impact as more are added.
It sounds like such a simple thing but I can't figure out how to plot this on a time axis when all I have is the Start Dates.
Derek, we're really going to need a sample packaged workbook to help you solve your problem.
... of try making your [Start Date] field continuous (green).
Been trying to do that. Like I said, it sounds simple...but it's not.
I have attached sample workbook. I also added some annotations so you can understand what I should be seeing. Please have a look and let me know.
Your time is very much appreciated!
Example.twbx 26.2 KB
No. I think you are not understanding the ask. I'm not so sure how else to explain it. It's a simple thing I am after...
Just think of Site "A" in the example...
That site should start out at zero (0) @ 06/01/2016 and increment by 100 each day beyond that day. (i.e. 5 days later I should see a savings of 500kWh in the line chart on 06/06/2016).
When other sites are added that would just accelerate the speed at which the lines goes up. As in my example, once 2 sites have been done, then from that point the line should rise by 200 each day.
I've included a simple table, with a simple workbook example and even a picture of how it should look. I don't know how else to explain.
Maybe one of the greats Joe Mako could look at this. It should be a walk in the park. It's not very complicated. I suspect we need to use INDEX() or LOOKUP() in some way but have been unable to figure it out yet.
I think I understand the issue now.
I can provide my approach, but need to prepare these data set.
This is the table to show date for all sites.
Edit data source and pivot data
previous_value(0)+running_SUM(zn(sum([Sheet1 (Book1)].[kWh Saved/Day])))
Missing_value_SM_9.0_V2.twbx 58.1 KB