quantity delta using a date slicer

[jc4]

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.

Any ideas? Thanks!

[PietroFarias]

I'm not Marco Russo on DAX, but i get to play.

Someone should be able to improve the performance of this DAX. But see if it helps.

LBS Delta :=
VAR QtRows =
    FILTER (
        Sales,
        Sales[As of Date] >= FIRSTDATE ( Sales[As of Date] )
            || Sales[As of Date] <= LASTDATE ( Sales[As of Date] )
    )
RETURN
    IF (
        HASONEFILTER ( Sales[As of Date] ),
        BLANK (),
        IF (
            COUNTROWS ( QtRows ) = 1,
            VALUES ( Sales[LBS - Ordered] ),
            CALCULATE ( SUM ( 'Sales'[LBS - Ordered] ), LASTDATE ( Sales[As of Date] ) )
                - CALCULATE ( SUM ( 'Sales'[LBS - Ordered] ), FIRSTDATE ( Sales[As of Date] ) )
        )
    )

[jc4]

I think this is working! Thank you so much! 

I think I understand what the VAR QtRows is doing. What is the first if statement doing in the return section?

[PietroFarias · Accepted solution]

 If you are referring to the ONEHASFILTER, in fact, it has no use. I checked it again and found it to be unpredictable. I removed the HASONEFILTER.

LBS Delta:=
VAR QtRows =
    FILTER (
        Sales;
        Sales[As of Date] >= FIRSTDATE ( Sales[As of Date] )
            || Sales[As of Date] <= LASTDATE ( Sales[As of Date] )
    )
RETURN
        IF (
            COUNTROWS ( QtRows ) = 1;
            VALUES ( Sales[LBS - Ordered] );
            CALCULATE ( SUM ( 'Sales'[LBS - Ordered] ); LASTDATE ( Sales[As of Date] ) )
                - CALCULATE ( SUM ( 'Sales'[LBS - Ordered] ); FIRSTDATE ( Sales[As of Date] ) )
        )