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