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

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

JulietZhu

I do not quite understand what the measure has to deliver. Could you explain a bit more?

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

I am trying to convert the following sql into DAX. For Aug, as of month end day, I need know toal task count, which is created in 30-60 days compared to 08312018.

 

Declare @Date char(8);

Set @Date='20180831';

select count(distinct case when datediff(d,convert(char(8),tm.CreatedDate,112),@Date) >=31 and datediff(d,convert(char(8),tm.CreatedDate,112),@Date)<=60

then tm.taskid else null end) TaskCreated31_60

FROM task tm WITH (NOLOCK)

WHERE convert(char(8),tm.CreatedDate,112) = @Date