Forum Discussion

dsandip's avatar
dsandip
Frequent Visitor
3 years ago
Solved

CALCULATE function is not working

Hi, I using below DAX: Cureent Quarter Sales = CALCULATE([Sales Amount],     FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1) ) and want to calculate values for [Current QTD] = 1 only and f...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    dsandip OK, it's incorrect but what is your definition of correct? Maybe this?

    Current Quarter Sales = 
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, CALCULATE([Sales Amount], FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1)), 0 )
RETURN
  __Result
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

dsandip You should be able to do this:

 

Cureent Quarter Sales = CALCULATE([Sales Amount], 'Calendar'[Current QTD] = 1)

or:

Current Quarter Sales = 
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, [Sales Amount], 0 )
RETURN
  __Result

 

 

dsandip's avatar
dsandip
Frequent Visitor
3 years ago

Hi,

Your first and 2nd Measure DAX is showing below result which is incorrect:

Measure 3 =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Calendar'[Current QTD] = 1 )
)
and 
Measure 2 =
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, [Sales Amount], 0 )
RETURN
  __Result
 
Thanks,
Sandip
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    dsandip OK, it's incorrect but what is your definition of correct? Maybe this?

    Current Quarter Sales = 
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, CALCULATE([Sales Amount], FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1)), 0 )
RETURN
  __Result
    • dsandip's avatar
      dsandip
      Frequent Visitor
      3 years ago

      Yes, it is working now. But I wonder that why it is not working for below DAX:

      Measure 5= CALCULATE([Sales Amount], FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1))
      like below:

      So, we need to use a if() with condition to show values for current quarter only and others to show 0. right?