Request help

[Anonymous]

Dear All,

I request your help to solve a scenario. I have a input table, using that I need to create a output report .I am having trouble in creating the dax formulas for columns Shares Count .The Report date should be as per the date which we are passing.

I have written the below DAX formula with some hardcoded date(2007,07,07) . How to implement date dynamically (I should have the option to chooses any date and should be able to see the result).

Shares Count = VAR myDate = DATE(2007, 07, 07)
RETURN
SUMX(

FILTER(CS,
CS[EntryDate] <= myDate && ( CS[ExitDate] >= mydate || CS[ExitDate] = BLANK()) && myDate>=CS[ValidFrom] && myDate<=CS[ValidTo]
),
'CS'[SharesCount]
)

Thank you in advance

Regards,

rnagalla25

[v-danhe-msft]

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

[Anonymous]

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 

[v-danhe-msft]

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

[v-danhe-msft · Accepted solution]

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

[Anonymous]

Hi @Anonymous

Create a Calendar table if its not there aleady in your model and make the proper relationship.

Create a slicer based on the Date column from Calendar table.

Assign this date to your variable.

VAR myDate = SELECTEDVALUE( Calendar_date[Date])

This way the user can select the date and this will be passed dynamically.

Thanks
Raj