Forum Discussion

GilesWalker's avatar
GilesWalker
Icon for Skilled Sharer rankSkilled Sharer
10 years ago
Solved

Earliest date do this otherwise do that?

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.

 

DateRailed Tonnage# WagonsLot #Ship NameShipped TonnageMoistureShipment #AdjustedBulk storage
1/07/2015                    813.35                       20.0019186                          9,466.94
2/07/2015                    739.93                       20.0019187                        10,206.87
3/07/2015                    763.86                       20.0019188                        10,970.73
4/07/2015                    799.92                       20.0019189                        11,770.65
6/07/2015                    813.76                       20.0019190                        12,584.41
7/07/2015                    752.96                       20.0019191                        13,337.37
8/07/2015    XXX                         5,106.00                  8.63 XXX                       8,231.37
10/07/2015                    819.26                       20.0019192                          9,050.63
11/07/2015                    828.86                       20.0019193                          9,879.49
14/07/2015                    804.77                       20.0019194                        10,684.26
15/07/2015                    796.69                       20.0019195                        11,480.95
16/07/2015                    535.40                       14.0019196                        12,016.35
6/08/2015                    813.56                       20.0019197                        12,829.91
14/08/2015                    574.89                       14.0019198                        13,404.80
20/07/2015                    804.62                       20.0019199                        14,209.42
21/07/2015    XXX                         7,522.00                  8.78 XXX                       6,687.42
22/07/2015                    797.90                       20.0019200                          7,485.32
25/07/2015                    714.63                       20.0019201                          8,199.95
26/07/2015                    739.07                       20.0019202                          8,939.02
27/07/2015                    812.04                       20.0019203                          9,751.06
29/07/2015                    811.03                       20.0019204                        10,562.09
30/07/2015                    782.85                       20.0019205                        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

2 Replies