SUMX and IF

[setis]

Dear experts,

I have a measure that captures the last date on a date slicer:

MaxSelectedDate = 
CALCULATE(
    MAX(Calendar_NAV[Date]);
    ALLSELECTED(Calendar_NAV[Date]))

Then I have a measure that calculates a value in Table 1. 

I need a measure that itinerates Table 1 and calculates the measure only if a the column "Date Closed" is after the above measure [MaxSelectedDate].

This is what I'm trying without success:

VAR Measure = My measure goes here
RETURN
SUMX('Table1';
CALCULATE(
    IF(
        'Table1'[Date closed]>[MaxSelectedDate];
        Measure;"")
        ))

Could somebody help please?

[v-alq-msft · Accepted solution]

Hi, @setis 

Based on your description, you may try to modify the measure as follows.

Measure = 
SUMX(
     ALLSELECTED('Table1');
     CALCULATE(
        IF(
        'Table1'[Date closed]>[MaxSelectedDate];
        My measure goes here;""
        )
     )
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nishantjain]

@setis 

When you use variable, they are not evaluated in the context filter you apply in the calculate function. Instead of putting your "Measure" as variable, you need to put it directly in the calcuate function

// Don't put your measure as variable if you are trying to change the context filter)
//VAR Measure = My measure goes here
RETURN
SUMX('Table1';
CALCULATE(
    IF(
        'Table1'[Date closed]>[MaxSelectedDate];
        My measure goes here;"")
        ))

I am not sure why you need to put it as a variable, but using variable within calcuate will not change the value return by the measure i.e. the context filter will not change. 

[setis]

@Tad17 thanks for your help.

@Nishantjain It seems like in the first argument of the IF, where you put 

'Table1'[Date closed]

 it only let me put a measure. 

Please see the report here reproducing the issue https://drive.google.com/file/d/1bQyBNs-7uu8e4sOiVRZnEkz57fKQXFMl/view?usp=sharing 

I need the slicer as it is, but I need to show in the measure only the values where the closed date is > to the max date of the slicer.

Thanks in advance

[Ashish_Mathur]

Hi,

This should get you started.  The figure in the Grand total is wrong but i hope someone sle can help you with it.  Download the PBI file from here.

[Tad17]

Hey @setis 

Try:

Calculate(SUM(Table[column],FILTER(Table1, Table1[Date Closed]>MaxSelectedDate))

or

=SUMX(FILTER(Table1, Table1[Date Closed]>MaxSelectedDate),[Column])

I'm not sure I got it quite right, but it should be close.

If this helps please kudo.

If this solves your problem please accept it as a solution.