Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous,

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.

View solution in original post

5 REPLIES 5
v-danhe-msft
Employee
Employee

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

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.
Anonymous
Not applicable

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 

 

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

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.

Hi @Anonymous,

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.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.