cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

quantity delta using a date slicer

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

 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] ) )
        )

View solution in original post

3 REPLIES 3
Highlighted
Resolver II
Resolver II

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

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] ) )
        )
    )
Highlighted

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?

Highlighted

 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] ) )
        )

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors