How to: if selected date is before/after the displayed date, then...

[Gjakova]

Hi there, I have the following issue:

I have a list of projects and I want to see if a project is closed or not.

So if I select the date: 20 January 2021 on my date slicer, I want to see the following result:

Project	End Date	Closed or Not Closed
A	5 January 2020	Closed
B	18 January 2021	Closed
C	1 February 2021	Not Closed
D	13 March 2021	Not Closed

And if I select the date 4 July 2021, I want to see the following result:

Project	End Date	Closed or Not Closed
A	5 January 2020	Closed
B	18 January 2021	Closed
C	1 February 2021	Closed
D	13 March 2021	Closed

I tried the following measure but it does not work good:

ProjectStatus =

VAR SelectedDate = SELECTEDVALUE(Calender[Date])

VAR ClosedOrNot=

       IF(MAX(Projects[end_date]) > SelectedDate, "Not Closed", "Closed")

RETURN

       ClosedOrNot

Does someone know a solution?

[pranit828 · Accepted solution]

HI @Gjakova 

try the below code 

ProjectStatus =
VAR SelectedDate = SELECTEDVALUE(Calender[Date])
VAR ClosedOrNot=
       IF(CALCULATE(MAX(Projects[end_date]), ALLEXCEPT(Projects,Projects[Project])) > SelectedDate, "Niet Closed", "Closed")
RETURN
       ClosedOrNot

[Gjakova]

Thanks so much! That seems to work. Could you perhaps explain your measure/solution so I can understand it better for the future?