Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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])
Perhaps something along the lines of:
Measure =
VAR __Max = MAXX(FILTER('Table',[Action] = "CARRYFORWARD"),[Row Number])
RETURN
SUMX(FILTER('Table',[Row Number] >= __Max),[Hours])
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!
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
63 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |