Forum Discussion

sokatenaj's avatar
sokatenaj
Advocate II
8 years ago

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 IDShares 1Vest Date 1Shares 2Vest Date 2Shares 3Vest Date 3
5551231,417.0005/01/20171,417.0005/01/20181,416.0005/01/2019
5551231,600.0007/01/20181,600.0007/01/20191,600.0007/01/2020
100000334.0005/01/2017333.0005/01/2018333.0005/01/2019
200000459.0005/01/2016458.0005/01/2017458.0005/01/2018
300000482.0007/01/2018482.0007/01/2019481.0007/01/2020
300000367.0005/01/2017367.0005/01/2018366.0005/01/2019
400000600.0007/01/2018600.0007/01/2019600.0007/01/2020
500000500.0009/30/20162,000.0009/30/20171,000.0009/30/2018
6000001,250.0009/30/20171,500.0009/30/20181,250.0009/30/2019

 

Any help would be great please. 

Thank you!

 

13 Replies

    • sokatenaj's avatar
      sokatenaj
      Advocate 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!!!!!!