Forum Discussion
Sum issue
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!
13 Replies
- parry2kSuper User
What expected output you are trying to achieve?
- sokatenajAdvocate II
parry2k The total # of shares of each employee based on the date range.
Basically, all I want to see the total shares each individual has on a date range of Nov 1 2017 up to December 31, 2022. Problem is that they get a batch of shares at each vest date. Plus some of the data has shares already vested which I don't want which is why I need the date range.
Many thanks!!!!!!
- parry2kSuper User
it required unpivot of data and then it is easy, i quickly put together pbix for your reference, change it as per your need.
https://drive.google.com/open?id=0B4dPgH9_BPBrOG9rR01HOGFjOGM