Forum Discussion
PatrickLamoste
Helper I
Helper ICalculate - 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/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 |
Perhaps something along the lines of:
Measure = VAR __Max = MAXX(FILTER('Table',[Action] = "CARRYFORWARD"),[Row Number]) RETURN SUMX(FILTER('Table',[Row Number] >= __Max),[Hours])
2 Replies
- Greg_Deckler
Community Champion
Perhaps something along the lines of:
Measure = VAR __Max = MAXX(FILTER('Table',[Action] = "CARRYFORWARD"),[Row Number]) RETURN SUMX(FILTER('Table',[Row Number] >= __Max),[Hours])
PatrickLamosteThank 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!