cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rnagalla25 Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Request help

Hi @rnagalla25,

Based on my test, you could refer to below steps:

1.Create relationship between the two tables.

1.PNG

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.

2.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

Re: Request help

Hi @rnagalla25

 

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

Community Support Team
Community Support Team

Re: Request help

Hi @rnagalla25,

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rnagalla25 Frequent Visitor
Frequent Visitor

Re: Request help

Hi @v-danhe-msft

 

Please refer the below tables for sample data set:

 

Company Table :

CompanyIDEntryDateExitDate
CFCE4E129/18/19752/13/2009
2C1AD0635/7/19926/23/1998
329B4CEA12/13/19905/31/1997
4E6FAE6010/26/198912/31/2002
D04873CA9/21/19731/29/2007
E8B67F9912/15/197712/31/1989
1D34918A12/6/19844/20/2011
EB166A5A4/14/19774/12/1979
66F2177812/12/199112/31/2003
6A7397213/19/2003NULL
13702BCD4/12/20002/27/2018
82DB173B10/16/19975/4/2017
D92E7DEB11/24/20143/10/2017
38D5758D6/22/199412/10/2015

 

Company Share Table :

 

CompanyIDCompanyShareIDSharesCountValidFromValidTo
D04873CA34DY0-1331/1/200512/31/2005
CFCE4E1235F82F81-A02/14/20096/30/2009
D04873CABB0F16-Y01/30/20076/18/2008
6A739721A2113287-086/19/20086/17/2009
CFCE4E12786ED127-H16/19/20082/13/2009
D92E7DEB4ECF059E-515/26/20163/10/2017
6A739721C3EEC626-B196/19/20146/17/2015
38D5758DA303255A-E16/21/20126/19/2013
82DB173BB9D65BC4-905/5/20175/23/2017
D92E7DEBA1FCD1AE-016/18/20155/25/2016
13702BCD67C6C94D-A11/1/200512/31/2005
82DB173BE09BF0BB-931/1/20066/21/2007
13702BCD2C9C17CE-216/21/20126/19/2013
D04873CA25D70-131/1/200312/31/2003
CFCE4E12CE8113AD-A11/1/200312/31/2003
6A739721419BBF6B-2136/24/20106/22/2011
D92E7DEB9742494B-7111/24/20146/18/2015
38D5758D83EDB013-516/23/20116/20/2012
38D5758DD4559066-711/1/200512/31/2005
82DB173BC3ECC2CA-727/1/20096/23/2010
82DB173BC09C2FD5-231/1/200512/31/2005
13702BCDAC63A140-711/1/200412/31/2004
13702BCD1EDE709E-116/24/20106/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 IDSharesShares Previous YearDifference

 

Appreciate your support.

 

Regards,

rnagalla25 

 

Community Support Team
Community Support Team

Re: Request help

Hi @rnagalla25,

Based on my test, you could refer to below steps:

1.Create relationship between the two tables.

1.PNG

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.

2.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Request help

Hi @rnagalla25,

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.