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, 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.