Project status based on tasks status and date of the report

[Antonin]

Hi everyone,

I am new in DAX and i need your help !

We have the report of all the project every month and I am creating a dashboard with a data history.

With this forum, i could manage to create the project status based on tasks status but not taking in consideration the date of the report :

For example for the project 1 in date of 22022, the answer should be Finished and not In progress.

Here my DAX :

Project Status = 
  VAR p = SUMMARIZE(FILTER('Table','Table'[Project Name]=EARLIER([Project Name])),'Table'[Tasks status])
RETURN
  IF(MAXX(p,'Table'[Tasks status])="Finished" && COUNTROWS(p)=1,"Finished","In progress")

Any help is appreciated, thank you so much!

Best regards,

Antonin

[ValtteriN]

Hi,

Try something like this:

var _project = ProjectStatus[Project] return

if(CONTAINS(FILTER(all(ProjectStatus),ProjectStatus[Project]=_project),ProjectStatus[Status],"Finished"),"Finished","NF")

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[VahidDM · Accepted solution]

Hi @Antonin 

Try this code to add a new column with DAX:

Project Status = 
VAR _T =
    CALCULATETABLE (
        FILTER ( 'Table', 'Table'[Task Status] <> "Finished" ),
        ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Project Name] )
    )
VAR _A =
    COUNTROWS ( _T )
RETURN
    IF ( ISBLANK ( _A ), "Finished", "In Progress" )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Antonin]

Hi @VahidDM,

Works perfectly, thank you so much !

Best regards,

Antonin