due date passed and status is not done

[Anonymous]

Hi All,

I'm trying to create a column that will show the due date, if the status is not done.

so the data I have is similar to this:

Due_date	Status
12/03/2022	New
13/03/2022	Inprogress
13/03/2022	Done
14/03/2022	Done
15/03/2022	Inprogress
16/03/2022	New

What I want to do is only capture those which are either new or inprogress, after the due date has passed, so in this example it would populate the first 2 and last 2.

I have this so far 

due date passed = IF(ISBLANK(Task[DUE_DATE]), BLANK(), if(Task[ISSUE_STATUS_NAME] = Task[Done], BLANK(), Task[DUE_DATE]))

but it errors, because DAX doesn't support type text with a type integer.

Can someone point out the error of my ways?

Thanks,

Jeff

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to this:

What I want to do is only capture those which are either new or inprogress, after the due date has passed, so in this example it would populate the first 2 and last 2.

It seems that you want to show rows where the date is not blank and the status <> Done, right?

If so, as @amitchandak mentioned, please create a flag measure ,and then apply it to visual-filter pane, set as "is 1":

Flag Measure = IF(MAX('Table'[Due_date])<>BLANK()&&MAX('Table'[Status])<>"Done",1,0)

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

[Anonymous]

@amitchandrak i see that if the due date field is empty, it puts a 1 in the new column, how can i get around that?

[Anonymous]

@amitchandak 

due date passed = IF(Task[DUE_DATE] <= TODAY() && Task[ISSUE_STATUS_NAME] <> "Done",1,0)

It populated the new column with 31/12/1899 😄

Which I do not understand at all

[amitchandak]

@Anonymous , Try a measure like

countrows(Filter(Table, Table[Due_date] <= today() && Table[Status] <> Done ))

[Anonymous]

@amitchandak 

Hi Amit,

due date = countrows(Filter(Task, Task[Due_date] <= today() && Task[ISSUE_STATUS_NAME] <> "Done" ))

it populated the field with 973, I'm guessing because there are 973 which match that query, I don't want a count of how many there are, but a way to flag entries that fail the either one of the queries

Thanks,

Jeff

[amitchandak]

@Anonymous , means a new column as flag

if( Table[Due_date] <= today() && Table[Status] <> "Done", 1, 0)

[Anonymous]

@amitchandrak that was my fault, had it set as a date.