Calculate - Sum with Multiple Criteria

[PatrickLamoste]

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/25/2019).  The formula goes: Row 10 plus Row 11 to 14 minus Row 15. The result should be 277.63. It would be great if the measure can work with a slicer.

Thank you so much!

Data Source:

Row Number	Accrual Type	Effective Date	Action	Hours
1	Banked Time	11/11/2019	CARRYFORWARD	181.88
2	Banked Time	15/11/2019	GRANT	6.75
3	Banked Time	17/11/2019	GRANT	21.00
4	Banked Time	18/11/2019	GRANT	10.50
5	Banked Time	19/11/2019	GRANT	6.00
6	Banked Time	20/11/2019	GRANT	11.25
7	Banked Time	21/11/2019	GRANT	6.00
8	Banked Time	22/11/2019	GRANT	12.00
9	Banked Time	24/11/2019	GRANT	17.25
10	Banked Time	25/11/2019	CARRYFORWARD	272.63
11	Banked Time	25/11/2019	GRANT	3.00
12	Banked Time	26/11/2019	GRANT	4.50
13	Banked Time	27/11/2019	GRANT	3.00
14	Banked Time	28/11/2019	GRANT	1.50
15	Banked Time	29/11/2019	TAKEN	-7.00

[Greg_Deckler · Accepted solution]

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]

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!