Forum Discussion

PatrickLamoste's avatar
PatrickLamoste
Helper I
6 years ago
Solved

Calculate - Sum with Multiple Criteria

Hello,   I am working on a rather tricky SQL table (see below) and now I am in a pickle.   I am trying to get the total hours without adding the values before the latest CARRYFORWARD action (11/2...
Need Help
Show and Tell
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Perhaps something along the lines of:

     

    Measure = 
  VAR __Max = MAXX(FILTER('Table',[Action] = "CARRYFORWARD"),[Row Number])
RETURN
  SUMX(FILTER('Table',[Row Number] >= __Max),[Hours])
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Perhaps something along the lines of:

 

Measure = 
  VAR __Max = MAXX(FILTER('Table',[Action] = "CARRYFORWARD"),[Row Number])
RETURN
  SUMX(FILTER('Table',[Row Number] >= __Max),[Hours])
PatrickLamoste's avatar
PatrickLamoste
Helper I
6 years ago

Thank you for this!

 

I ended up splitting the table and changed the formula a little bit and added a USERELATIONSHIP so I can limit the max date according to my slicer.

 

 

Measure = 
  VAR __Max = MAXX(FILTER('Table',[Action] = "CARRYFORWARD"),[Effective Date])
RETURN
  CALCULATE(SUMX(FILTER('Table',[Effective Date] >= __Max),[Hours]), USERELATIONSHIP([Effective Date],DateDim[Date]))

 

 

Thanks again!