Forum Discussion
Request help
Hi Anonymous,
Based on my test, you could refer to below steps:
1.Create relationship between the two tables.
2.Create measures:
2006 = VAR myDate = DATE(2006, 07, 07)
RETURN
CALCULATE(SUM(Table2[SharesCount]),
FILTER('Table2',myDate>='Table2'[ValidFrom] && myDate<=Table2[ValidTo]),FILTER(CS,
CS[EntryDate] <= myDate && ( CS[ExitDate] >= mydate || CS[ExitDate] = BLANK())))2005 = VAR myDate = DATE(2005, 07, 07)
RETURN
CALCULATE(SUM(Table2[SharesCount]),
FILTER('Table2',myDate>='Table2'[ValidFrom] && myDate<=Table2[ValidTo]),FILTER(CS,
CS[EntryDate] <= myDate && ( CS[ExitDate] >= mydate || CS[ExitDate] = BLANK())))Difference=[2006]-[2005]
3.Create a Table visual and add related fields, now you can see the result.
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/0nqh0ayxz840dru/Request%20help.pbix?dl=0
Regards,
Daniel He
Hi v-danhe-msft
Please refer the below tables for sample data set:
Company Table :
| CompanyID | EntryDate | ExitDate |
| CFCE4E12 | 9/18/1975 | 2/13/2009 |
| 2C1AD063 | 5/7/1992 | 6/23/1998 |
| 329B4CEA | 12/13/1990 | 5/31/1997 |
| 4E6FAE60 | 10/26/1989 | 12/31/2002 |
| D04873CA | 9/21/1973 | 1/29/2007 |
| E8B67F99 | 12/15/1977 | 12/31/1989 |
| 1D34918A | 12/6/1984 | 4/20/2011 |
| EB166A5A | 4/14/1977 | 4/12/1979 |
| 66F21778 | 12/12/1991 | 12/31/2003 |
| 6A739721 | 3/19/2003 | NULL |
| 13702BCD | 4/12/2000 | 2/27/2018 |
| 82DB173B | 10/16/1997 | 5/4/2017 |
| D92E7DEB | 11/24/2014 | 3/10/2017 |
| 38D5758D | 6/22/1994 | 12/10/2015 |
Company Share Table :
| CompanyID | CompanyShareID | SharesCount | ValidFrom | ValidTo |
| D04873CA | 34DY0-13 | 3 | 1/1/2005 | 12/31/2005 |
| CFCE4E12 | 35F82F81-A | 0 | 2/14/2009 | 6/30/2009 |
| D04873CA | BB0F16-Y | 0 | 1/30/2007 | 6/18/2008 |
| 6A739721 | A2113287-0 | 8 | 6/19/2008 | 6/17/2009 |
| CFCE4E12 | 786ED127-H | 1 | 6/19/2008 | 2/13/2009 |
| D92E7DEB | 4ECF059E-5 | 1 | 5/26/2016 | 3/10/2017 |
| 6A739721 | C3EEC626-B | 19 | 6/19/2014 | 6/17/2015 |
| 38D5758D | A303255A-E | 1 | 6/21/2012 | 6/19/2013 |
| 82DB173B | B9D65BC4-9 | 0 | 5/5/2017 | 5/23/2017 |
| D92E7DEB | A1FCD1AE-0 | 1 | 6/18/2015 | 5/25/2016 |
| 13702BCD | 67C6C94D-A | 1 | 1/1/2005 | 12/31/2005 |
| 82DB173B | E09BF0BB-9 | 3 | 1/1/2006 | 6/21/2007 |
| 13702BCD | 2C9C17CE-2 | 1 | 6/21/2012 | 6/19/2013 |
| D04873CA | 25D70-1 | 3 | 1/1/2003 | 12/31/2003 |
| CFCE4E12 | CE8113AD-A | 1 | 1/1/2003 | 12/31/2003 |
| 6A739721 | 419BBF6B-2 | 13 | 6/24/2010 | 6/22/2011 |
| D92E7DEB | 9742494B-7 | 1 | 11/24/2014 | 6/18/2015 |
| 38D5758D | 83EDB013-5 | 1 | 6/23/2011 | 6/20/2012 |
| 38D5758D | D4559066-7 | 1 | 1/1/2005 | 12/31/2005 |
| 82DB173B | C3ECC2CA-7 | 2 | 7/1/2009 | 6/23/2010 |
| 82DB173B | C09C2FD5-2 | 3 | 1/1/2005 | 12/31/2005 |
| 13702BCD | AC63A140-7 | 1 | 1/1/2004 | 12/31/2004 |
| 13702BCD | 1EDE709E-1 | 1 | 6/24/2010 | 6/22/2011 |
I need to calculate shares and previous year shares as per any given date (dynamically)
example : If Date = 2017/07/07
2017 shares should get reflected under "shares column" and 2016 shares should reflect under "previous year shares column"
Shown below is the expected output of the report:
| company ID | Shares | Shares Previous Year | Difference |
Appreciate your support.
Regards,
rnagalla25
Hi Anonymous,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He