I have a table that contains quantities on contract for each customer by day. I'm trying to build a report that will display the increase or decrease on contract by customer where the user can select the date range via a slicer. Right now I'm using the formula below and it works great if the contract already exists in the date range selected:
LBS Delta = CALCULATE(SUM('Sales'[LBS - Ordered]),LASTDATE('Sales'[As of Date])) - CALCULATE(SUM('Sales'[LBS - Ordered]),FIRSTDATE('Sales'[As of Date]))
The problem I'm running into is when a contract is created between the dates selected. For example, if the dates selected are 4/1/18 to 4/5/18 but the contract is created on the 2nd for 20 LBS the formula would return 20 - 20 = 0. What I'd like to have happen is it return the value on the 1st instead of the 2nd resulting in the formula returning 20 - 0 = 20.
A possible solution would be to compare the LASTDATE of the line to the LASTEDATE of the selection and if they are not the same to return 0. I'm not sure what the sytax would look like for this.