cancel
Showing results for
Did you mean:
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]
)

Regards,

rnagalla25

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Request help

Hi @rnagalla25,

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.

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
Highlighted
Super Contributor

## Re: Request help

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

Super Contributor

## 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.
Frequent Visitor

## Re: Request help

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

Regards,

rnagalla25

Super Contributor

## Re: Request help

Hi @rnagalla25,

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.

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.
Super Contributor

## Re: Request help

Hi @rnagalla25,

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.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 27 members 825 guests
Recent signins: