How to get a dynamic calculated column according selected value

[Geek]

Here is the thing i need : 

First, i have facts table that is called factsProjectFollowUp and time table that is called dimTime, so there is one line for each date between two date. 
The facts table is follow up on project. There is many columns. ID, Date Created, Start Date, End Date, Satus. So, what we see in this list is always the actual status. 
The need i have is to create a report that will be filtered by financial year. So, this field is in the time table. My problem is that sometimes the date created, start date and end date could be in different financial year. So, a task could be start during last financial year and finish during this one. So, what i will like is to be able to show the task as in progress during last financial year but show as in progress during this one until it is completed. When, they are started and completed in the past financial year, it is fine to show them as completed. If a project is started in 2022-2023 and not completed, it needs to be report as in progress during 2023-2024. Let's say that the status is Not yet started when it is created until there is a date in start date. Then, it will be In progress until there is a end date. Then, at this time, the project will be completed. Maybe there is some tranformations that needs to be done in powerquery before. So is there anyone who know what to do. So, i will like to see the as they were in the financial year selected and not they are in the actual list.

help me please. 

[Anonymous]

Hi @Geek ,

I suggest you to create a measure to get dynamic result.

My Sample:

Data model:

Measure:

Dynamic Status =
VAR _RANGESTART =
    MIN ( DimDate[Date] )
VAR _RANGEEND =
    MAX ( DimDate[Date] )
VAR _STARTDATE =
    SELECTEDVALUE ( 'Table'[Start Date] )
VAR _ENDDATE =
    SELECTEDVALUE ( 'Table'[End Date] )
RETURN
    IF (
        _STARTDATE > _RANGEEND,
        "Not Start",
        IF (
            _STARTDATE <= _RANGEEND
                && _ENDDATE >= _RANGEEND,
            "In Progress",
            "Completed"
        )
    )

Result is as below.

Best Regards,
Rico Zhou

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

[Geek]

Hi there, 

Thank you so much for answer, but unfortunately on my side it duplicates ID for many dates instead of it to be on one line