Count volume if one date is before and one date is after specified date

[tspearman]

I am completely stumped by this - I hope this is within PBI capability as would be fantastically useful.

I have a ship, that loads cargo at A, B and C. It then discharges at B, C, and D. I want to know what volume of cargo I have on the ship at A, B, C and D.

The data I have is:

Load	Ldate	Discharge	Ddate	Volume
A	1/01/2019	B	5/01/2019	3
B	5/01/2019	D	28/01/2019	6
C	14/01/2019	D	28/01/2019	4
A	1/01/2019	C	14/01/2019	21
C	14/01/2019	D	28/01/2019	3
A	1/01/2019	B	5/01/2019	6
B	5/01/2019	C	14/01/2019	65
A	1/01/2019	D	28/01/2019	23

I am looking to create a measure(s) that will yeild the below:

Port	Volume
A	53
B	115
C	36
D	0

Any help much appreciated

[OwenAuger · Accepted solution]

@tspearman 

Here is my suggestion on how to set this up (I have attached a PBIX).

1. Transform your Load/Discharge data into this form, by splitting Load & Discharge into two tables then appending them into a single table. I called this table Cargo

   Note that Volume Signed is positive for Load and negative for Discharge.

2. Create a Date table and a Location table. Location just contains distinct Locations i.e. A,B,C,D.
3. Data model looks like this:
4. Create these measures:
   

   Volume Cumulative = 
   VAR GlobalMaxCargoDate = CALCULATE ( MAX ( Cargo[Date] ), ALL () )
   RETURN
       IF (
           MIN ( 'Date'[Date] ) <= GlobalMaxCargoDate,
           CALCULATE(
               SUM ( Cargo[Volume Signed] ),
               DATESBETWEEN('Date'[Date], BLANK(), MAX('Date'[Date]))
           )
       )
   
   Volume Cumulative up to Filtered Location = 
   CALCULATE(
       [Volume Cumulative],
       SUMMARIZE ( Cargo, 'Date'[Date] ),
       ALL ( Location ) // If your model is more complex, you may need to be more specific about what is in this ALL
   )

   The 2nd measure grabs the Dates that are currently visible in the Cargo table, turns them into a Date[Date] filter, then calculates [Volume Cumulative] as at those dates (effectively the maximum of those dates), with the Location filter cleared with ALL.
5. Now you can visualize [Volume Cumulative up to Filtered Location] filtered by Location[Location]

Please post back if needed.

Regards,

Owen