Forum Discussion
Helper IVDAX help for Count task based on aging bucket
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
)
)
)
Community ChampionOne 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
Helper IVAlB, 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.
- 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 AugThat 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
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, 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
)
)
)