Earliest date do this otherwise do that?

[GilesWalker]

Hi everyone,

I am sure there is a way to do this in Power BI but I just cant seem to figure it out. I have an excel document I have joined together in PBI. There are three commoditites Zinc, Lead, and Copper. The table is structured like this:

On my report I have two slicers; month and commodity. The graph then shows the Railed tonnage as bars and I want a line over the top to show the total Bulk storage. The bulk storage is made up of railed tonnage minus any shipped tonnes.

The issue I have is that at the beggining of a month there is potential for the bulk storage to have leftover in it. I cant figure out how to get a measure to be able to calculate that for the first date (given the monthly filter) take the number in bulk storage and then for any consecutive dates only add on the railed tonnes and deduct any shipped tonnes. Essentially the first date is the starting point.

Here is an excel example.

Date	Railed Tonnage	# Wagons	Lot #	Ship Name	Shipped Tonnage	Moisture	Shipment #	Adjusted	Bulk storage
1/07/2015	813.35	20.00	19186						9,466.94
2/07/2015	739.93	20.00	19187						10,206.87
3/07/2015	763.86	20.00	19188						10,970.73
4/07/2015	799.92	20.00	19189						11,770.65
6/07/2015	813.76	20.00	19190						12,584.41
7/07/2015	752.96	20.00	19191						13,337.37
8/07/2015				XXX	5,106.00	8.63	XXX		8,231.37
10/07/2015	819.26	20.00	19192						9,050.63
11/07/2015	828.86	20.00	19193						9,879.49
14/07/2015	804.77	20.00	19194						10,684.26
15/07/2015	796.69	20.00	19195						11,480.95
16/07/2015	535.40	14.00	19196						12,016.35
6/08/2015	813.56	20.00	19197						12,829.91
14/08/2015	574.89	14.00	19198						13,404.80
20/07/2015	804.62	20.00	19199						14,209.42
21/07/2015				XXX	7,522.00	8.78	XXX		6,687.42
22/07/2015	797.90	20.00	19200						7,485.32
25/07/2015	714.63	20.00	19201						8,199.95
26/07/2015	739.07	20.00	19202						8,939.02
27/07/2015	812.04	20.00	19203						9,751.06
29/07/2015	811.03	20.00	19204						10,562.09
30/07/2015	782.85	20.00	19205						11,344.94

Column Bulk storage does what I want. In this case the starting point is 8653.59 (9466.96-813.35) tonnes and the end point is 11344.94 tonnes. Now in my graph if I had filters on to show July and August then the starting point is 8653.59 and the bulk storage will just add on any new railed tonnes and deduct any shipped tonnes. If the filter was changed to be August then the starting point 11344.94. 

I hope this makes sense.

Thanks,

Giles

[austinsense]

I'm about to go to bed or I would write an answer - This is totally possible, just try to build each piece one at a time.

[GilesWalker · Accepted solution]

@austinsense Thanks for responding to this. I asked this question in a different format and got the answer I needed.

http://community.powerbi.com/t5/Desktop/Calculate-carry-over-from-prior-months/m-p/33133#M11701

Regards,

Giles