Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Folks,
Trying to do something with 6 stock periods, though the table below only shows 3. I have employees that have 6 columns of shares with different vest dates. What I want to do is Sum across the total shares and then add a date parameter to it, but I can't quite get it to work as a measure. This is what I have:
1 Shares =
2 CALCULATE (
3 SUMX(Stock,Stock[Shares 1]+Stock[Shares 2]+Stock[Shares 3]+Stock[Shares 4]+Stock[Shares 5]),
4 'Stock'[Vest Date 1] >= DATE ( 2017, 11, 1 ) && 'Stock'[Vest Date 1] <= DATE ( 2022, 12, 31 ),
5 'Stock'[Vest Date 2] >= DATE ( 2017, 11, 1 ) && 'Stock'[Vest Date 2] <= DATE ( 2022, 12, 31 ),
6 'Stock'[Vest Date 3] >= DATE ( 2017, 11, 1 ) && 'Stock'[Vest Date 3] <= DATE ( 2022, 12, 31 ),
7 'Stock'[Vest Date 4] >= DATE ( 2017, 11, 1 ) && 'Stock'[Vest Date 4] <= DATE ( 2022, 12, 31 ),
8 'Stock'[Vest Date 5] >= DATE ( 2017, 11, 1 ) && 'Stock'[Vest Date 5] <= DATE ( 2022, 12, 31 ),
9 )
I know the first 3 lines are correct and I know if I just used 1 date line and then closed it with a ")" it works based on just Vest Date 1, but I need to add those other dates because there may be a chance where they are all different. One thing to note is that each share group is released on the vest date. For example, Shares 1 would be linked to Stock[Vest Date 1].
This is how the data is laid out on the table. The key to the master table is empl ID:
Empl ID | Shares 1 | Vest Date 1 | Shares 2 | Vest Date 2 | Shares 3 | Vest Date 3 |
555123 | 1,417.00 | 05/01/2017 | 1,417.00 | 05/01/2018 | 1,416.00 | 05/01/2019 |
555123 | 1,600.00 | 07/01/2018 | 1,600.00 | 07/01/2019 | 1,600.00 | 07/01/2020 |
100000 | 334.00 | 05/01/2017 | 333.00 | 05/01/2018 | 333.00 | 05/01/2019 |
200000 | 459.00 | 05/01/2016 | 458.00 | 05/01/2017 | 458.00 | 05/01/2018 |
300000 | 482.00 | 07/01/2018 | 482.00 | 07/01/2019 | 481.00 | 07/01/2020 |
300000 | 367.00 | 05/01/2017 | 367.00 | 05/01/2018 | 366.00 | 05/01/2019 |
400000 | 600.00 | 07/01/2018 | 600.00 | 07/01/2019 | 600.00 | 07/01/2020 |
500000 | 500.00 | 09/30/2016 | 2,000.00 | 09/30/2017 | 1,000.00 | 09/30/2018 |
600000 | 1,250.00 | 09/30/2017 | 1,500.00 | 09/30/2018 | 1,250.00 | 09/30/2019 |
Any help would be great please.
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |