DAX help for Count task based on aging bucket

[JulietZhu]

I have total task created in selected month. Also I need calcuated task aging bucket. For example, for aging bucket 30-60, I need count taskid if datediff of (createddate and month end day) <60 and datediff of (createddate and month end day) > 30.

 The following DAX is used, but it is not working. Please advice. Thanks

TaskCreated30-60 =
var _SelectDate=SELECTEDVALUE('Date'[Date],max('Date'[Date]))
return
If (Datediff(Task[createddate],_selectedDate,day) >= 31 && Datediff(Task[createddate],_selectedDate,day)<60, 

    ,CALCULATE(Task[TaskCreated]))

Dummy data link

https://1drv.ms/u/s!AlYpYKwSuOKxhFduK3PetlnD08b1

[AlB]

Hi @JulietZhu

Out of curiosity, what are you trying to do with the outer CALCULATE?

[JulietZhu]

@AlB, sorry, typo. Need remove outer calculate

[AlB]

@JulietZhu

Have you edited the formula for the measure? It's different now from what you had in the beginning and what appears on the file

[JulietZhu]

Yes, I edit DAX, but don't know how to update in one drive. Please use the following dax

TaskCreated30-60 =
var _SelectDate=SELECTEDVALUE('Date'[Date],max('Date'[Date]))
return
If (Datediff(Task[createddate],_selectedDate,day) >= 31 && Datediff(Task[createddate],_selectedDate,day) <60,CALCULATE(Task[TaskCreated]))

[AlB]

@JulietZhu

If this is a measure and not a calculated column, you cannot reference "naked columns" since you do not have a row context. This latest version will not work. I think the previous one looked better.  

[AlB]

@@JulietZhu

Ok, I am not versed in sql but how about this? It is almost what you had in the beginning, only adding the ALL(). Otherwise if you are using the month slicer set to August as you have in the file report, the measure will only be checking Tasks created in August and thus none will fall into the 31-60 category.    

TaskCreated30-60 =
VAR _SelectDate =
    SELECTEDVALUE ( 'Date'[Date], MAX ( 'Date'[Date] ) )
RETURN
    CALCULATE (
        [TaskCreated],
        ALL ( 'Date' ),
        DATEDIFF ( Task[createddate], _SelectDate, DAY ) >= 31
            && DATEDIFF ( Task[createddate], _SelectDate, DAY ) <= 60
    )

If you're only interested in August you can just set the date manually as below and forget about the month slicer. In this case you would not need the ALL() 

TaskCreated30-60 =
VAR _SelectDate =
    DATE(2018,08,31)    
RETURN
    CALCULATE (
        [TaskCreated],
        DATEDIFF ( Task[createddate], _SelectDate, DAY ) >= 31
            && DATEDIFF ( Task[createddate], _SelectDate, DAY ) <= 60
    )

[AlB]

@JulietZhu

One more small detail.

It is considered best practice not to use the table name to refer to measures. For instance, you have a [TaskCreated] measure that you were referring to as Task[TaskCreated]. It is not incorrect and it will work but it is better to leave the table name out so that you can readily distinguish a measure from a calculated column when reading the code. You do use the table name in a calculated column, as you already did in Task[createddate].

Best

[JulietZhu]

@AlB, first, thanks for your reply.

I tried your DAX, the number is not correct. Total number for TaskCreated30-60  should be about 3100. But your DAX gives 54094. 

[AlB]

Hi  @JulietZhu

If you look at the Task table, filter the created column manually for days between 07/02/2018  (08/31/2018 - 60) and 07/31/2018 (08/31/2018 - 31) and look at the number of distinct values on TaskId, you get exactly what my measure yields: 54094.

If this is what you are after, it would be correct.

Maybe the sql code does something else?   

[JulietZhu]

@AlB Sorry for not explain very clearly. I should not use task created as example. Please find new dummy data in the following link    https://1drv.ms/u/s!AlYpYKwSuOKxhFlbVxbyyLdhax9Z

Basically I am looking for oustanding task of each month end and task count for each aging bucket. For example, Aug 2018, by end of month at 08/31/2018, how many tasks are still outstanding and open. In those tasks, how many tasks are created in 30 days? How many tasks are created between 31 and 60 days? Same thing for other aging bucket. Adding all the task counts from aging bucket shoud equal to task outstanding total as of 08/31/2018. For sql script runnig, task counts for 31-60 aging bucket  gives number of 3094. Hopefully this time should be clear. 

I twisted your DAX to TaskOutstanding 30-60, but the number of 1405 is still not correct. Please give advice. Thanks.

[AlB]

@JulietZhu

Let's start from the beginning

What is, exactly, an oustanding task?

What is, exactly, an open task?

[JulietZhu]

@AlB, task oustanding is open task, which is still in open status and need work on to close. Less oustanding is good and less task aging in more than 90 days is good also.

For example in Aug 2018,
calcuation for task outstanding = open task in the begin day (08/01/2018) + task created on Aug - task completed on Aug

That will be oustadning task in the month end of 08/31/2018.

If you download new file and see DAX , you can have more understadning about how outstadning task comes. Thanks

[AlB]

@JulietZhu

So an oustanding task as of a specific day is a task that was created before or on that day (as indicated on the column [createdDate] and that it has not yet been completed (as indicated on the column [completeddate]) as of that day.  Correct?

What about the [taskstatusid] column. What is that for?

[JulietZhu]

@AlB, taskstatusID is primary key in taskstatus table. TaskStatusDI of 1,2,3, means task status of 'complete', 'in progress' and 'not start'.  Sorry for complicated business logic.

Also, you understanding about outstadning task is correct.

[JulietZhu · Accepted solution]

@AlB, I would be able to figure it out my self.  I put DAX here as your reference. Thanks for your help 

TaskOutstanding 30-60 = 
VAR EndDate = MAX ( 'Date'[Date] )
RETURN
IF (MIN ( 'Date'[Date] )<= CALCULATE ( MAX ('Task'[CreatedDate]), ALL ('Date') ), CALCULATE ([TaskOutstanding],FILTER ( ALL ( 'date'[Date] ), 'Date'[Date] <= EndDate ),
KEEPFILTERS (
IFERROR (DATEDIFF (Task[CreatedDate] , EndDate, DAY ),( DATEDIFF ( EndDate, Task[CreatedDate], DAY ) ) * -1) <= 60 &&
IFERROR(DATEDIFF (Task[CreatedDate] , EndDate, DAY ),(DATEDIFF ( EndDate, Task[CreatedDate], DAY ) ) * -1) >=31
)
)
)

[JulietZhu]

@AlB,  TaskOutstanding = Task[TaskBegDay]+[TaskCreated]-[TaskCompleted]. It is very smiple DAX.   DAX for TaskBegDay and TaskCompleted is are complex and took me days to get solution.

[AlB]

@JulietZhu

Cool. Well done. What's the code for the measure  [TaskOutstanding]

 prefer not having to download the file again.

[AlB]

@JulietZhu

I do not quite understand what the measure has to deliver. Could you explain a bit more?

[JulietZhu]

I am trying to convert the following sql into DAX. For Aug, as of month end day, I need know toal task count, which is created in 30-60 days compared to 08312018.

Declare @Date char(8);

Set @Date='20180831';

select count(distinct case when datediff(d,convert(char(8),tm.CreatedDate,112),@Date) >=31 and datediff(d,convert(char(8),tm.CreatedDate,112),@Date)<=60

then tm.taskid else null end) TaskCreated31_60

FROM task tm WITH (NOLOCK)

WHERE convert(char(8),tm.CreatedDate,112) = @Date