Forum Discussion

JulietZhu's avatar
JulietZhu
Icon for Helper IV rankHelper IV
7 years ago
Solved

DAX help for Count task based on aging bucket

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 d...
Need Help
  • JulietZhu's avatar
    JulietZhu
    7 years ago

    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's avatar
JulietZhu
Icon for Helper IV rankHelper IV
7 years ago

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's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
    JulietZhu
    Icon for Helper IV rankHelper IV
    7 years ago

    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's avatar
    JulietZhu
    Icon for Helper IV rankHelper IV
    7 years ago

    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
    )
    )
    )

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    JulietZhu

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

     prefer not having to download the file again.

     

  • JulietZhu's avatar
    JulietZhu
    Icon for Helper IV rankHelper IV
    7 years ago

    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.