I'm trying to write an expression for "Integrator Revenue" measure based on the following conditions from the example table:
Earliest Send Date aggregated on a Customer and Platform level must always be later than Integration date (based on the whole dataset, not just selected time frame)
If first condition is met, and Service is not "SMS" or "Voice", just calculate the Integrator Revenue (Revenue*RevenueShare/100) for the selected time frame
If first condition is met, and if Service is "SMS" or "Voice", check if SUM(GrossProfit)/SUM(Revenue)*100>=RevenueShare and then calculate the Integrator Revenue (Revenue*RevenueShare/100) for the selected time frame
if none of the conditions are met, Integrator Revenue should be 0
Customer
Integrator
Platform
Service
Request Type Id
Interactions
Revenue
Gross Profit
Earliest Send Date
Integration Date
Revenue Share %
2
Integrator1
Platform1
SMS
1
57.134
1.472,40
236,55 €
2020-04-22
2022-08-01
5
13
Integrator2
Platform2
SMS
1
31.237
749,04
124,27 €
2022-07-13
2021-12-15
5
32
Integrator1
Platform2
SMS
1
26.705
402,03
125,93 €
2022-01-27
2021-12-15
5
58
Integrator2
Platform2
SMS
1
7.170
398,66
317,86 €
2023-04-22
2021-12-15
5
59
Integrator2
Platform3
SMS
1
303.944
195,22
-4,03 €
2022-11-07
2023-03-01
5
61
Integrator1
Platform2
SMS
1
7.162
145,93
8,06 €
2021-01-06
2021-12-15
5
85
Integrator1
Platform1
SMS
1
2.716
103,58
22,00 €
2021-05-10
2022-08-01
5
102
Integrator1
Platform2
SMS
1
5.534
93,92
29,15 €
2022-04-22
2021-12-15
5
104
Integrator1
Platform4
SMS
1
4.554
74,13
33,71 €
2022-06-15
2022-11-30
5
106
Integrator2
Platform2
SMS
1
946
59,50
0,33 €
2022-09-16
2021-12-15
5
113
Integrator2
Platform2
SMS
1
2.573
58,93
8,89 €
2022-04-08
2021-12-15
5
122
Integrator1
Platform2
SMS
1
2.034
48,57
5,45 €
2022-03-14
2021-12-15
5
130
Integrator1
Platform2
SMS
1
2.889
44,73
17,99 €
2020-01-01
2021-12-15
5
136
Integrator2
Platform2
SMS
1
3.684
37,17
34,77 €
2020-09-15
2021-12-15
5
160
Integrator2
Platform2
SMS
1
108.327
30,45
10,04 €
2022-02-10
2021-12-15
5
166
Integrator1
Platform4
SMS
1
691
28,17
15,05 €
2022-02-18
2022-11-30
5
166
Integrator2
Platform2
SMS
1
11.935
20,38
16,45 €
2022-09-30
2021-12-15
5
174
Integrator1
Platform2
SMS
1
998
19,31
1,03 €
2021-08-20
2021-12-15
5
175
Integrator1
Platform2
SMS
1
856
17,60
1,63 €
2021-02-05
2021-12-15
5
175
Integrator1
Platform2
SMS
1
726
16,70
2,08 €
2022-04-08
2021-12-15
5
178
Integrator1
Platform2
SMS
1
1.912
16,08
5,48 €
2023-01-02
2021-12-15
5
178
Integrator1
Platform2
SMS
1
38.419
14,87
4,42 €
2022-02-10
2021-12-15
5
181
Integrator2
Platform2
SMS
1
921
11,25
3,98 €
2023-05-30
2021-12-15
5
205
Integrator1
Platform2
SMS
1
184
11,04
8,24 €
2020-01-01
2021-12-15
5
215
Integrator2
Platform2
SMS
1
363
10,14
0,38 €
2022-09-27
2021-12-15
5
219
Integrator2
Platform2
SMS
1
318
8,27
3,10 €
2020-01-09
2021-12-15
5
227
Integrator1
Platform2
SMS
1
2.696
8,17
6,68 €
2020-01-01
2021-12-15
5
232
Integrator1
Platform2
SMS
1
186
7,09
2,69 €
2021-11-24
2021-12-15
5
253
Integrator1
Platform2
SMS
1
602
6,72
4,34 €
2021-12-07
2021-12-15
5
254
Integrator2
Platform2
SMS
1
174
6,70
4,72 €
2022-11-28
2021-12-15
5
266
Integrator1
Platform2
SMS
1
5.529
3,39
1,52 €
2021-05-19
2021-12-15
5
283
Integrator2
Platform2
SMS
1
415
1,68
0,37 €
2023-05-09
2021-12-15
5
316
Integrator2
Platform2
Viber
39
1.211
1,51
0,16 €
2022-06-28
2021-12-15
5
323
Integrator1
Platform2
SMS
1
96
1,44
0,32 €
2021-10-06
2021-12-15
5
323
Integrator2
Platform2
SMS
1
346
1,37
1,37 €
2023-05-23
2021-12-15
5
325
Integrator2
Platform2
SMS
1
943
1,27
0,71 €
2022-09-28
2021-12-15
5
335
Integrator2
Platform2
SMS
1
27
0,64
0,64 €
2023-06-21
2021-12-15
5
350
Integrator2
Platform2
SMS
1
240
0,55
0,46 €
2023-03-10
2021-12-15
5
352
Integrator2
Platform2
SMS
1
176
0,50
0,46 €
2020-01-01
2021-12-15
5
355
Integrator1
Platform2
SMS
1
969
0,50
0,24 €
2020-01-02
2021-12-15
5
369
Integrator1
Platform2
SMS
1
138
0,46
0,32 €
2020-01-02
2021-12-15
5
385
Integrator1
Platform2
SMS
1
24
0,34
0,15 €
2022-06-07
2021-12-15
5
387
Integrator2
Platform2
SMS
1
23
0,29
0,19 €
2023-04-18
2021-12-15
5
395
Integrator2
Platform4
SMS
1
5
0,20
0,09 €
2022-02-18
2022-11-30
5
403
Integrator2
Platform2
SMS
1
5
0,14
0,01 €
2023-02-10
2021-12-15
5
404
Integrator2
Platform4
SMS
1
4
0,07
0,02 €
2022-05-24
2022-04-01
5
414
Integrator2
Platform2
SMS
1
1
0,04
0,01 €
2023-06-10
2021-12-15
5
421
Integrator1
Platform2
SMS
1
3
0,02
0,00 €
2020-11-13
2021-12-15
5
422
Integrator2
Platform2
SMS
1
18
0,01
0,00 €
2020-01-02
2021-12-15
5
I tried many things, but could not get it to work properly, so hoping someone would have an idea. 🙂
