Show project if active during the selected year (slicer)

[ychang]

I apologize if this exists already, but I am trying to use the table visual to just show projects that have been active during the year selected using the slicer. In other words, I would like to only include projects with start year less than or equal to selected year and end year greater than or equal to the selected year. I have tried to do this by creating a column with the following dax: 

Active? = and(Project[Start_Date__c].[Year]<=value(SELECTEDVALUE('Reporting Period'[Year__c])),Project[End Date__c].[Year]>=value(SELECTEDVALUE('Reporting Period'[Year__c])))

I am starting to realize however that the values of a column probably does not respond to the selected slicer values. Does anybody know a workaround for this? Thank you

[v-kelly-msft]

Hi @ychang ,

Duplicate table Reporting Period[Year_c],calling table 2,use a dax expression as below:

Table 2 = DISTINCT('Reporting Period'[Year_c])

Create relationships between  the 3 tables:

Using a measure as below:

Measure = 
var a=IF(SELECTEDVALUE('Project'[Start_Date_c]) in VALUES('Table 2'[Year_c]),SELECTEDVALUE('Project'[Start_Date_c]),BLANK())
var b=IF(SELECTEDVALUE('Project'[End_Date_c]) in VALUES ('Reporting Period'[Year_c]),SELECTEDVALUE('Project'[End_Date_c]),BLANK())
Return
IF(a<>BLANK()&&b<>BLANK(),1,0)

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Turn it into a measure and use it as a filter perhaps?

Active? = and(MAX(Project[Start_Date__c].[Year])<=value(SELECTEDVALUE('Reporting Period'[Year__c])),MAX(Project[End Date__c].[Year])>=value(SELECTEDVALUE('Reporting Period'[Year__c])))

Do you really need VALUE? Are your years text? 

[ychang]

Thank you for your reply! The year field is a text field - I was a bit lazy and did not convert it to number at the time but have done so since. I also have tried to make this into a measure and adding it as a field in the table visual, but it seems to be calculating across all data instead of just for a specific project. 

[ychang]

As for the option of adding another table, the year field in the reporting period are not actually dates so what I did instead is just make another table with all the active dates for each project kind of like this:

With a table of projects like this...

Project	Start Date	End Date
Project A	10/1/2019	10/3/2019
Project B	9/20/2019	10/2/2019

I created another table that looked something like this:

Project	Active Date
Project A	10/1/2019
Project A	10/2/2019
Project A	10/3/2019
Project B	9/20/2019
Project B	9/21/2019
Project B	...
Project B	10/1/2019
Project B	10/2/2019

My thinking is that with this, I should be able to create a column/measure to check whether the related table contains a date from the year that is selected with the slicer, but I am wondering if this is feasible and if so what my next steps should be.