Forum Discussion
Request help
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
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
5 Replies
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
RajHi 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