Forum Discussion

JulietZhu's avatar
JulietZhu
Helper 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
    )
    )
    )

AlB's avatar
AlB
Community Champion
7 years ago

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

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

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

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

        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?