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 Anonymous,
From your description, I could not figure out the data of [ValidFrom] and [ValidTo] column, could you please post me more pictures about your data stracture or offer me some sample data to test for your problem?
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,
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 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