Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |