Forum Discussion

MCALDERON's avatar
MCALDERON
Frequent Visitor
5 years ago
Solved

DAX - Need Help

Hi, I need some guidance on this new calculated column. I have this example of data. 

You can see I have 2 reapeted items, which have two different states, I need to calculate how much time passed from the date when the state was "Done" till the date when the state was "Closed". 

This is the way I thought it might work, but the column is blank.

var closeCase = if('Brazil Compliance Modernization'[State]= "Closed", 'Brazil Compliance Modernization'[Date])
var doneCase = IF('Brazil Compliance Modernization'[State] = "Done", 'Brazil Compliance Modernization'[Date])
return
DATEDIFF(closeCase,doneCase,DAY)
 
Please can you guide me on this?
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    MCALDERON , Try a new column like

     


    var closeCase = minx(filter('Brazil Compliance Modernization', [work_item_id] =earlier([work_item_id]) && 'Brazil Compliance Modernization'[State]= "Closed") 'Brazil Compliance Modernization'[Date])
    var doneCase = maxx(filter('Brazil Compliance Modernization', [work_item_id] =earlier([work_item_id]) && 'Brazil Compliance Modernization'[State] = "Done"), 'Brazil Compliance Modernization'[Date])
    return
    if( [status]= "Done", DATEDIFF(closeCase,doneCase,DAY) , blank())

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    MCALDERON , Try a new column like

     


    var closeCase = minx(filter('Brazil Compliance Modernization', [work_item_id] =earlier([work_item_id]) && 'Brazil Compliance Modernization'[State]= "Closed") 'Brazil Compliance Modernization'[Date])
    var doneCase = maxx(filter('Brazil Compliance Modernization', [work_item_id] =earlier([work_item_id]) && 'Brazil Compliance Modernization'[State] = "Done"), 'Brazil Compliance Modernization'[Date])
    return
    if( [status]= "Done", DATEDIFF(closeCase,doneCase,DAY) , blank())