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, sorry, typo. Need remove outer calculate

 

 

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

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

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