Forum Discussion

jc4's avatar
jc4
Frequent Visitor
8 years ago
Solved

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...
  • PietroFarias's avatar
    PietroFarias
    8 years ago

     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] ) )
        )
PietroFarias's avatar
PietroFarias
Resolver II
8 years ago

I'm not Marco Russo on DAX, but i get to play. :smileyvery-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] ) )
        )
    )
  • jc4's avatar
    jc4
    Frequent Visitor
    8 years ago

    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's avatar
      PietroFarias
      Resolver II
      8 years ago

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