Status of something based on (selected) date

[Gjakova]

Could someone help me out with this situation? I need to check if a project is closed or not based on a current date (selection):

1. If the status of the project = active, then the project is not closed

2. if the status of the project = closed, then I need to see if the end date lays before the selected date, if yes: closed, if not: not closed

Project closed on selected date? = 
VAR SelectedDate =
    SELECTEDVALUE ( Calender[Date] ) 
VAR ClosedOrNot =
    IF (
        CALCULATE (
            MAX ( Projects[EndDateProject] ),
            ALLEXCEPT ( Projects, Projects[EndDateProject] )
        ) > SelectedDate,
        "Not closed",
        "Closed"
    ) 
VAR RelationOn =
    CALCULATE (
        ClosedOrNot,
        USERELATIONSHIP ( Calender[Date], Projects[EndDateProject] )
    ) 
RETURN
    ClosedOrNot

This measure works only when I compare the enddate vs my date column in a table, but I would like to know the status of the project also when I just select a date, instead of putting it up against another date.

Someone who has a suggestion?

[v-deddai1-msft · Accepted solution]

Hi @Gjakova ，

I doubt about what’s your expected output. Do you want to compare the max end date of the product with your selected date in calendar table to determine if the product is closed?

Would you please try to use the following measure:

Project closed on selected date? = 
VAR SelectedDate =
    MAX('Calendar'[Date])
VAR ClosedOrNot =
    IF (
        CALCULATE (
            MAX ( financials[Date] ),
            ALLEXCEPT(financials,financials[Product]),REMOVEFILTERS('Calendar')
        ) > SelectedDate,
        "Not closed",
        "Closed"
    ) 
RETURN
    ClosedOrNot

You can also refer to the pbix file, if it doesn't meet your requirement, please do more explanation.

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

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @Gjakova ,

Would you please show us some sample data and your expected output by onedrive for business? Then we can help you more accurately.

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

Best Regards,

Dedmon Dai

[Gjakova]

https://www.dropbox.com/s/nwycr5l2wmbyp3i/PBI_community_post_Measures.pbix?dl=0

See above for a sample I created based on the standard financials data from the Power BI sample set.
I added another column, basically I want to see the status of a country or product or something else and see what the status of that country or project was on that given date.

In my real dataset I have 100k + services which are either closed or not closed. They have a startdate and an enddate. If the project = closed + the enddate of the product lays after my selected date/timeperiod, then it is not closed, else it is closed.

I hope this sample helps a bit 🙂 Thanks so much for your help!