Hello,
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. 🙂
Thanks a lot in advance!
