Selectedvalue date between two dates

[ArchieEric]

Hi

I would like to make a column on my table that shows whether the selected value table from a Date table is between twos date fields on the main table.

There is no relationship between the date table and the main table.

My Dax code currently is.

SelectedMasters =

VAR selectedmonth =

    selectedvalue('Months'[Month Year])

RETURN

    if(

        'ContinuousService'[ContractStart] <= selectedmonth &&

        'ContinuousService'[ContractEnd] >= selectedmonth

        , 1, 0)

which returns nothing, no 1s and no 0s.

Thanks in advance

[Anonymous · Accepted solution]

Hi @ArchieEric 

Please try the following Measure:

Measure = IF(SELECTEDVALUE(CalendarTable[Date]) >= SELECTEDVALUE('Table'[Start]) && SELECTEDVALUE(CalendarTable[Date]) <= SELECTEDVALUE('Table'[End]) , 1 , 0)

Count rows:

Count = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Measure]=1))

By using measure, you can dynamically calculate the total number of rows based on the different dates you choose.

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArchieEric]

Excellent thank you

seemed to be the extra use of SELECTEDVALUE on the main table field that did it?

[Anonymous]

Hi @ArchieEric

Based on your needs, I have created the following table.

CalendarTabe:(from 1/1/2010 to 10/29/2024)

CalendarTable = CALENDAR(DATE(2010,1,1),DATE(2024,10,29))

DateTable:

Then use the following Dax:

Measure = 
VAR _select_date = SELECTEDVALUE('Table'[Date])
VAR _max_date = MAX('CalendarTable'[Date])
VAR _min_date = MIN('CalendarTable'[Date])

RETURN
IF(_select_date >= _min_date && _select_date <= _max_date ,1,0)

If you choose date from 1/1/2010 to 10/29/2024

 

If you choose date from 1/26/2018 to 10/29/2024

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArchieEric]

I think I need a column rather than a measure

Id like a 4th column on the table to say whether the selected month is between the ContractStartMonth and ContractEndMonth and id like to assess it row by row

if there are 100 rows id like a card to say on 50 of them the selected month falls between the dates

[Anonymous]

Hi @ArchieEric 

Are you only considering the month, or do you need to take the year into account as well?

Please provide your sample data in a copyable format, rather than a screenshot.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArchieEric]

sorry im new to this, how do i do it copyable?

GAR30 2011-02-01 2016-02-01
GAR30 2016-02-01 2018-01-01
GAR30 2018-02-01 2023-02-01
GAR32 2003-07-01 2008-07-01
GAR32 2008-07-01 2008-09-01
GAR32 2008-10-01 2012-12-01
GAR32 2012-12-01 2017-11-01
GAR32 2021-09-01 2028-01-01
GAV01 1997-01-01 1997-11-01
GAV01 1998-01-01 1998-10-01
GAV01 1999-01-01 1999-10-01
GAV01 2000-01-01 2014-05-01
GAV01 2014-06-01 2016-03-01
GAV01 2022-03-01 2025-03-01
GAY01 1995-01-01 1996-01-01
GAY01 1997-01-01 1999-10-01
GAY01 2000-01-01 2000-10-01
GAY01 2001-01-01 2001-10-01
GAY01 2002-01-01 2002-10-01
GAY01 2003-01-01 2003-10-01
GAY01 2004-01-01 2004-10-01
GAY01 2005-01-01 2005-10-01
GAY01 2006-01-01 2007-01-01
GAY01 2007-01-01 2008-01-01
GAY01 2008-01-01 2009-01-01
GAY01 2009-01-01 2016-12-01
GAY01 2016-12-01 2017-12-01
GAY01 2021-06-01 2026-06-01
GAY02 2001-04-01 2004-01-01
GAY02 2004-04-01 2005-01-01
GAY02 2005-04-01 2006-01-01
GAY02 2006-04-01 2007-04-01
GAY02 2007-04-01 2008-04-01
GAY02 2008-04-01 2009-04-01
GAY02 2009-04-01 2012-04-01
GAY02 2012-04-01 2013-04-01
GAY02 2013-04-01 2014-04-01
GAY02 2014-04-01 2015-04-01
GAY02 2015-04-01 2016-04-01
GAY02 2016-04-01 2017-04-01
GAY02 2024-04-01 2025-04-01
GEC02 2004-09-01 2010-07-01
GEC02 2010-08-01 2015-08-01
GEC02 2019-11-01 2023-04-01
GED01 1998-11-01 2001-08-01
GED01 2001-11-01 2002-08-01
GED01 2002-11-01 2003-08-01
GED01 2003-11-01 2004-08-01
GED01 2004-11-01 2005-08-01
GED01 2005-11-01 2006-11-01
GED01 2006-11-01 2007-11-01
GED01 2007-11-01 2012-11-01
GED01 2012-11-01 2017-02-01
GED01 2022-11-01 2028-08-01
GEL04 2003-07-01 2006-06-01
GEL04 2006-07-01 2016-06-01
GEL04 2016-07-01 2017-06-01
GEL04 2022-07-01 2023-07-01
GER04 1999-03-01 2001-12-01
GER04 2002-03-01 2002-12-01
GER04 2003-03-01 2003-12-01
GER04 2004-03-01 2004-12-01
GER04 2005-03-01 2005-12-01
GER04 2006-03-01 2016-10-01
GER04 2022-12-01 2023-09-01
GIB01 1992-11-01 1994-11-01
GIB01 1994-12-01 1999-10-01
GIB01 1999-12-01 2001-10-01
GIB01 2001-10-01 2004-07-01
GIB01 2004-10-01 2005-07-01
GIB01 2005-10-01 2008-10-01
GIB01 2008-10-01 2015-10-01
GIB01 2015-10-01 2016-09-01
GIB01 2024-10-01 2025-10-01
GIF03 2004-01-01 2008-10-01
GIF03 2009-01-01 2014-01-01
GIF03 2021-02-01 2023-04-01
GIL27 2001-04-01 2004-01-01
GIL27 2004-04-01 2005-01-01
GIL27 2005-04-01 2006-01-01
GIL27 2006-04-01 2007-04-01
GIL27 2007-04-01 2008-04-01
GIL27 2008-04-01 2009-03-01
GIL27 2009-04-01 2012-04-01
GIL27 2012-04-01 2015-10-01
GIL27 2021-10-01 2024-10-01
GIL33 2004-05-01 2009-05-01
GIL33 2009-05-01 2010-05-01
GIL33 2010-05-01 2011-05-01
GIL33 2011-05-01 2012-05-01
GIL33 2012-05-01 2013-05-01
GIL33 2013-05-01 2014-05-01
GIL33 2014-05-01 2015-05-01
GIL33 2015-05-01 2016-05-01
GIL33 2016-05-01 2017-05-01
GIL33 2017-12-01 2023-02-01
GKL01 2004-04-01 2010-04-01
GKL01 2010-04-01 2011-04-01
GKL01 2011-04-01 2012-04-01
GKL01 2012-04-01 2013-04-01
GKL01 2013-04-01 2015-04-01
GKL01 2015-04-01 2015-04-01
GKL01 2017-11-01 2018-01-01
GKL01 2022-03-01 2028-01-01
GLA23 2004-03-01 2008-09-01
GLA23 2009-03-01 2014-03-01
GLA23 2014-03-01 2015-03-01
GLA23 2015-03-01 2016-03-01
GLA23 2016-03-01 2017-03-01
GLA23 2024-03-01 2025-03-01
GLE31 1999-07-01 2002-03-01
GLE31 2002-07-01 2003-04-01
GLE31 2003-07-01 2004-04-01
GLE31 2004-07-01 2005-04-01
GLE31 2005-07-01 2006-04-01
GLE31 2006-07-01 2007-07-01
GLE31 2007-07-01 2014-06-01
GLE31 2014-06-01 2015-06-01
GLE31 2015-06-01 2016-06-01
GLE31 2016-06-01 2017-06-01
GLE31 2018-01-01 2023-06-01
GLE31 2024-01-01 2025-01-01
GLE46 2000-03-01 2000-12-01
GLE46 2001-03-01 2001-12-01
GLE46 2002-03-01 2005-03-01
GLE46 2005-06-01 2006-03-01
GLE46 2006-06-01 2007-06-01
GLE46 2007-06-01 2008-06-01
GLE46 2008-06-01 2009-06-01
GLE46 2009-06-01 2010-06-01
GLE46 2010-06-01 2011-06-01
GLE46 2011-06-01 2013-06-01
GLE46 2013-06-01 2014-06-01
GLE46 2014-06-01 2015-06-01
GLE46 2015-06-01 2016-06-01
GLE46 2016-06-01 2017-06-01
GLE46 2022-06-01 2027-10-01
GLE59 2003-07-01 2009-04-01
GLE59 2009-05-01 2016-05-01
GLE59 2016-05-01 2017-05-01
GLE59 2024-05-01 2025-05-01
GLE64 2004-02-01 2008-10-01
GLE64 2008-10-01 2013-10-01
GLE64 2013-10-01 2014-10-01
GLE64 2014-10-01 2015-10-01
GLE64 2015-10-01 2016-10-01
GLE64 2018-10-01 2024-04-01
GLE72 2004-08-01 2009-08-01
GLE72 2010-06-01 2015-11-01
GLE72 2021-11-01 2022-11-01
GLE72 2024-02-01 2027-02-01
GLI04 2004-04-01 2009-04-01
GLI04 2009-04-01 2019-07-01
GLI04 2022-07-01 2024-05-01
GLJ01 2000-05-01 2003-02-01
GLJ01 2003-05-01 2004-01-01
GLJ01 2004-05-01 2005-02-01
GLJ01 2005-05-01 2006-02-01
GLJ01 2006-05-01 2007-05-01
GLJ01 2007-05-01 2008-05-01
GLJ01 2008-05-01 2016-07-01
GLJ01 2016-07-01 2017-06-01
GLJ01 2024-07-01 2025-07-01
GLJ02 2002-03-01 2004-12-01
GLJ02 2005-03-01 2005-12-01
GLJ02 2006-03-01 2007-03-01
GLJ02 2007-03-01 2008-03-01
GLJ02 2008-03-01 2009-03-01
GLJ02 2009-03-01 2010-03-01
GLJ02 2010-03-01 2011-03-01
GLJ02 2011-03-01 2012-03-01
GLJ02 2012-03-01 2013-03-01
GLJ02 2013-03-01 2014-03-01
GLJ02 2014-03-01 2016-04-01
GLJ02 2016-04-01 2017-02-01
GLJ02 2024-07-01 2025-06-01
GLO36 2003-02-01 2005-06-01
GLO36 2006-02-01 2007-02-01
GLO36 2007-02-01 2008-02-01
GLO36 2008-02-01 2009-02-01
GLO36 2009-02-01 2014-11-01
GLO36 2014-11-01 2029-01-01
GLO40 2003-07-01 2008-07-01
GLO40 2008-07-01 2017-07-01
GLO40 2020-07-01 2023-07-01
GLO41 2003-08-01 2008-11-01
GLO41 2008-11-01 2025-03-01
GLO48 2004-09-01 2009-09-01
GLO48 2009-09-01 2016-03-01
GLO48 2021-09-01 2027-03-01
GNC01 2000-09-01 2003-06-01
GNC01 2003-09-01 2004-06-01
GNC01 2004-09-01 2005-06-01
GNC01 2005-09-01 2008-09-01
GNC01 2008-09-01 2008-12-01
GNC01 2009-01-01 2009-08-01
GNC01 2009-08-01 2014-08-01
GNC01 2015-07-01 2018-03-01
GNC01 2019-08-01 2029-09-01
GNR01 2001-03-01 2003-12-01
GNR01 2004-03-01 2004-09-01
GNR01 2004-09-01 2009-05-01
GNR01 2010-05-01 2017-07-01
GNR01 2017-08-01 2024-07-01
GOB03 2004-01-01 2007-01-01
GOB03 2007-01-01 2016-10-01
GOB03 2020-01-01 2023-11-01
GOD07 2004-02-01 2009-03-01
GOD07 2009-04-01 2014-04-01
GOD07 2014-04-01 2015-04-01
GOD07 2015-04-01 2016-04-01
GOD07 2016-04-01 2017-04-01
GOD07 2020-04-01 2021-09-01
GOD07 2023-11-01 2027-05-01
GOL10 1995-08-01 1998-08-01
GOL10 1998-08-01 2001-05-01
GOL10 2001-08-01 2002-05-01
GOL10 2002-08-01 2003-05-01
GOL10 2003-08-01 2004-05-01
GOL10 2004-08-01 2005-05-01
GOL10 2005-08-01 2006-08-01
GOL10 2006-08-01 2007-08-01
GOL10 2007-08-01 2008-08-01
GOL10 2008-08-01 2023-05-01
GOL10 2023-07-01 2026-07-01
GOL38 2001-07-01 2004-04-01
GOL38 2004-07-01 2005-04-01
GOL38 2005-07-01 2006-04-01
GOL38 2006-07-01 2011-01-01
GOL38 2011-01-01 2014-01-01
GOL38 2014-01-01 2017-01-01
GOL38 2020-01-01 2023-02-01
GOO25 2003-06-01 2006-03-01
GOO25 2006-06-01 2007-06-01
GOO25 2007-06-01 2008-06-01
GOO25 2008-06-01 2009-06-01
GOO25 2009-06-01 2009-08-01
GOO25 2009-08-01 2014-08-01
GOO25 2024-08-01 2029-08-01
GOV01 1995-02-01 1997-12-01
GOV01 1998-02-01 1998-11-01
GOV01 1999-02-01 1999-11-01
GOV01 2000-02-01 2000-11-01
GOV01 2001-02-01 2001-11-01
GOV01 2002-02-01 2002-11-01
GOV01 2003-02-01 2003-11-01
GOV01 2004-02-01 2004-11-01
GOV01 2005-02-01 2005-11-01
GOV01 2006-02-01 2007-02-01
GOV01 2007-02-01 2012-12-01
GOV01 2013-01-01 2016-01-01
GOV01 2024-01-01 2026-01-01
GRA101 2002-12-01 2005-09-01
GRA101 2005-12-01 2015-11-01
GRA101 2015-12-01 2016-11-01
GRA101 2023-12-01 2024-12-01
GRA106 2003-02-01 2005-11-01
GRA106 2006-02-01 2007-02-01
GRA106 2007-02-01 2008-02-01
GRA106 2008-02-01 2017-09-01
GRA106 2021-03-01 2026-10-01
GRA16 1995-06-01 1996-06-01
GRA16 1997-07-01 2000-04-01
GRA16 2000-07-01 2001-04-01
GRA16 2001-07-01 2002-03-01
GRA16 2002-07-01 2003-04-01
GRA16 2003-07-01 2004-04-01
GRA16 2004-07-01 2005-04-01
GRA16 2005-07-01 2006-04-01
GRA16 2006-07-01 2014-04-01
GRA16 2014-07-01 2016-06-01
GRA16 2016-07-01 2017-06-01
GRA16 2024-07-01 2025-07-01
GRA31 1997-02-01 1999-11-01
GRA31 2000-02-01 2003-11-01
GRA31 2004-02-01 2004-11-01
GRA31 2005-02-01 2005-11-01
GRA31 2006-02-01 2007-02-01
GRA31 2007-02-01 2015-01-01
GRA31 2015-02-01 2016-01-01
GRA31 2016-02-01 2017-02-01
GRA31 2023-10-01 2026-10-01
GRA39 1998-03-01 1998-12-01
GRA39 1999-03-01 1999-12-01
GRA39 2000-03-01 2000-12-01
GRA39 2001-03-01 2002-02-01
GRA39 2002-03-01 2004-12-01
GRA39 2005-03-01 2010-12-01
GRA39 2010-12-01 2011-12-01
GRA39 2011-12-01 2012-12-01
GRA39 2012-12-01 2013-12-01
GRA39 2013-12-01 2014-12-01
GRA39 2014-12-01 2015-12-01
GRA39 2015-12-01 2016-12-01
GRA39 2017-12-01 2018-09-01
GRA39 2021-11-01 2024-11-01
GRA72 2000-10-01 2009-03-01
GRA72 2009-04-01 2016-04-01
GRA72 2022-04-01 2028-04-01
GRA77 2000-12-01 2003-09-01
GRA77 2003-12-01 2004-09-01
GRA77 2004-12-01 2005-09-01
GRA77 2005-12-01 2006-12-01
GRA77 2006-12-01 2009-01-01
GRA77 2009-01-01 2014-01-01
GRA77 2014-01-01 2015-01-01
GRA77 2015-01-01 2016-01-01
GRA77 2016-01-01 2017-01-01
GRA77 2017-01-01 2017-04-01
GRA77 2022-04-01 2027-04-01
GRA82 2001-06-01 2002-06-01
GRA82 2002-08-01 2008-09-01
GRA82 2009-01-01 2014-05-01
GRA82 2022-05-01 2026-05-01
GRA95 2002-08-01 2005-05-01
GRA95 2005-06-01 2010-06-01
GRA95 2010-08-01 2015-08-01
GRA95 2020-08-01 2025-08-01
GRA99 2002-10-01 2005-07-01
GRA99 2005-10-01 2006-10-01
GRA99 2006-10-01 2007-10-01
GRA99 2007-10-01 2008-09-01
GRA99 2008-10-01 2011-10-01
GRA99 2011-10-01 2017-04-01
GRA99 2017-04-01 2023-12-01
GRA99 2023-12-01 2024-12-01
GRE110 2003-01-01 2005-10-01
GRE110 2006-01-01 2007-01-01
GRE110 2007-01-01 2008-01-01
GRE110 2008-01-01 2009-01-01
GRE110 2009-01-01 2012-09-01
GRE110 2012-09-01 2015-09-01
GRE110 2024-08-01 2027-09-01
GRE111 2003-02-01 2010-10-01
GRE111 2010-11-01 2015-11-01
GRE111 2020-11-01 2025-11-01
GRE125 2003-11-01 2008-09-01
GRE125 2008-09-01 2018-07-01
GRE125 2023-01-01 2028-07-01
GRE130 2004-05-01 2007-04-01
GRE130 2007-04-01 2012-04-01
GRE130 2012-04-01 2013-04-01
GRE130 2013-04-01 2015-04-01
GRE130 2015-04-01 2016-04-01
GRE130 2016-04-01 2017-04-01
GRE130 2024-04-01 2029-10-01
GRE26 1997-02-01 1999-11-01
GRE26 2000-02-01 2001-02-01
GRE26 2001-06-01 2004-03-01
GRE26 2004-06-01 2005-03-01
GRE26 2005-06-01 2006-03-01
GRE26 2006-06-01 2007-06-01
GRE26 2007-06-01 2008-06-01
GRE26 2008-06-01 2009-09-01
GRE26 2009-09-01 2012-12-01
GRE26 2012-12-01 2016-03-01
GRE26 2022-09-01 2025-12-01
GRE52 1999-07-01 2002-03-01
GRE52 2002-07-01 2003-04-01
GRE52 2003-07-01 2004-04-01
GRE52 2004-07-01 2005-04-01
GRE52 2005-07-01 2006-04-01
GRE52 2006-07-01 2009-06-01
GRE52 2009-07-01 2012-07-01
GRE52 2012-07-01 2013-07-01
GRE52 2013-07-01 2014-07-01
GRE52 2014-07-01 2015-07-01
GRE52 2015-07-01 2016-07-01
GRE52 2020-05-01 2025-07-01
GRE73 2000-12-01 2003-09-01
GRE73 2003-12-01 2004-09-01
GRE73 2004-12-01 2005-09-01
GRE73 2005-12-01 2008-07-01
GRE73 2008-12-01 2014-12-01
GRE73 2014-12-01 2017-06-01
GRE73 2023-01-01 2029-03-01
GRE85 2001-07-01 2004-04-01
GRE85 2004-07-01 2005-04-01
GRE85 2005-07-01 2006-04-01
GRE85 2006-07-01 2007-07-01
GRE85 2007-07-01 2008-07-01
GRE85 2008-07-01 2011-02-01
GRE85 2011-02-01 2012-02-01
GRE85 2012-02-01 2013-02-01
GRE85 2013-02-01 2015-02-01
GRE85 2015-02-01 2016-02-01
GRE85 2016-02-01 2017-02-01
GRE85 2024-02-01 2025-02-01
GRE95 2002-03-01 2004-12-01
GRE95 2005-03-01 2005-12-01
GRE95 2006-03-01 2007-02-01
GRE95 2007-03-01 2010-02-01
GRE95 2010-02-01 2015-08-01
GRE95 2022-06-01 2027-08-01
GRE97 2002-06-01 2005-03-01
GRE97 2005-06-01 2006-03-01
GRE97 2006-06-01 2013-05-01
GRE97 2013-06-01 2025-11-01
GRI04 1994-05-01 1997-04-01
GRI04 1997-05-01 1998-02-01
GRI04 1998-05-01 1999-02-01
GRI04 1999-05-01 2000-02-01
GRI04 2000-05-01 2001-02-01
GRI04 2001-05-01 2002-02-01
GRI04 2002-05-01 2003-02-01
GRI04 2003-05-01 2004-01-01
GRI04 2004-05-01 2005-02-01
GRI04 2005-05-01 2006-02-01
GRI04 2006-05-01 2007-05-01
GRI04 2007-05-01 2007-08-01
GRI04 2007-08-01 2010-08-01
GRI04 2010-08-01 2011-08-01
GRI04 2011-08-01 2012-08-01
GRI04 2012-08-01 2013-08-01
GRI04 2013-08-01 2018-07-01
GRI04 2019-09-01 2019-11-01
GRI04 2021-12-01 2027-08-01
GRI15 2002-02-01 2004-11-01
GRI15 2005-02-01 2005-11-01
GRI15 2006-02-01 2007-02-01
GRI15 2007-02-01 2008-02-01
GRI15 2008-02-01 2009-02-01
GRI15 2009-02-01 2010-02-01
GRI15 2010-02-01 2011-02-01
GRI15 2011-02-01 2016-12-01
GRI15 2019-11-01 2026-12-01
GRO26 2001-05-01 2008-07-01
GRO26 2008-10-01 2015-05-01
GRO26 2015-05-01 2016-05-01
GRO26 2016-05-01 2017-05-01
GRO26 2023-08-01 2028-08-01
GRO40 2004-09-01 2009-09-01
GRO40 2009-09-01 2015-05-01
GRO40 2020-05-01 2025-10-01
GSA01 1998-12-01 2001-09-01
GSA01 2001-12-01 2004-11-01
GSA01 2005-02-01 2006-02-01
GSA01 2006-02-01 2009-08-01
GSA01 2009-08-01 2014-01-01
GSA01 2014-01-01 2015-07-01
GSA01 2018-07-01 2023-07-01
GUA17 2004-09-01 2009-09-01
GUA17 2009-09-01 2014-11-01
GUA17 2019-11-01 2029-09-01
GWS01 2002-09-01 2018-04-01
GWS01 2023-10-01 2029-03-01
HAD03 1997-12-01 2000-09-01
HAD03 2000-12-01 2001-09-01
HAD03 2001-12-01 2002-09-01
HAD03 2002-12-01 2003-09-01
HAD03 2003-12-01 2004-09-01
HAD03 2004-12-01 2005-09-01
HAD03 2005-12-01 2006-08-01
HAD03 2006-08-01 2009-08-01
HAD03 2009-08-01 2010-08-01
HAD03 2010-08-01 2011-08-01
HAD03 2011-08-01 2012-08-01
HAD03 2012-08-01 2012-09-01
HAD03 2012-09-01 2015-09-01
HAD03 2021-05-01 2024-01-01
HAD16 2003-09-01 2009-05-01
HAD16 2009-06-01 2012-05-01
HAD16 2012-06-01 2026-06-01
HAD20 2004-10-01 2009-10-01
HAD20 2011-11-01 2016-05-01
HAD20 2021-05-01 2026-05-01
HAG07 2004-03-01 2010-03-01
HAG07 2010-03-01 2017-02-01
HAG07 2023-02-01 2026-02-01
HAI16 2001-11-01 2004-08-01
HAI16 2004-11-01 2005-08-01
HAI16 2005-11-01 2006-11-01
HAI16 2006-11-01 2007-11-01
HAI16 2007-11-01 2008-11-01
HAI16 2008-11-01 2011-11-01
HAI16 2011-11-01 2012-11-01
HAI16 2012-11-01 2023-02-01
HAI25 2003-04-01 2006-11-01
HAI25 2006-11-01 2007-11-01
HAI25 2007-11-01 2008-07-01
HAI25 2008-07-01 2017-07-01
HAI25 2018-07-01 2029-03-01
HAL13 2010-02-01 2018-11-01
HAL13 2018-11-01 2021-11-01
HAL13 2021-12-01 2024-12-01
HAL22 1998-09-01 2001-06-01
HAL22 2001-09-01 2002-06-01
HAL22 2002-09-01 2003-06-01
HAL22 2003-09-01 2004-06-01
HAL22 2004-09-01 2005-06-01
HAL22 2005-09-01 2009-01-01
HAL22 2009-02-01 2016-05-01
HAL22 2019-04-01 2020-04-01
HAL22 2020-06-01 2029-07-01
HAL28 1999-05-01 2002-02-01
HAL28 2002-05-01 2002-10-01
HAL28 2002-10-01 2004-10-01
HAL28 2005-10-01 2006-10-01
HAL28 2006-10-01 2007-10-01
HAL28 2007-10-01 2008-07-01

[ArchieEric]

Thanks a lot for the reply BTW

[ArchieEric]

Im nut sure how i build a relationship between the two tables, the 'Months'[Month Year] field doesnt directly relate to any field in the 'ContinuousService' table

the 'Months'[Month Year] field is a date and i want to pick rows from the 'ContinuousService' table when the selected [Month Year] field is between the [ContractStart] field and the [ContractEnd] Field

[shafiz_p]

Hi @ArchieEric  It looks like your DAX code isn’t returning the expected results because the SELECTEDVALUE function might not be working as intended without a relationship between the tables. You can try this code:

SelectedMasters = 
VAR selectedmonth = SELECTEDVALUE('Months'[Month Year])
RETURN
    IF (
        NOT ISBLANK(selectedmonth) &&
        'ContinuousService'[ContractStart] <= selectedmonth &&
        'ContinuousService'[ContractEnd] >= selectedmonth,
        1,
        0
    )

It will  return 0 if selectedmonth is blank. I would suggest try develop relationship using virtual relationship function TREATAS if possible.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz