Forum Discussion

Frequent Visitor

3 years ago

Custom Column with todays date that ages until it get close

Hi everyonen! Am having trouble creatng a custom column that will make it possible to track how long a task has been open and group them in how many days its been open. For this I have a st...

Frequent Visitor

3 years ago

Thank you so much!

But I have another issue. Now it has only caculated the time the task has been open until i closes. I want to know for how long its open like live? and when it closes ist no longer apart of the aging graph

If that makes since.

BA_Pete
Super User
3 years ago
I think I know what you mean.

You want to be able to show a chart something like this showing how many tasks were open at any given time?

If so, then this is best done with a DAX measure.

You'll need an UNrelated calendar table, and a measure something like this:

_noofOpenTasks = VAR __cDate = MAX(calendar[date]) RETURN CALCULATE( DISTINCTCOUNT(yourTable[taskID]), FILTER( yourTable, yourTable[start_date] <= __cDate && (yourTable[close_date] > __cDate || ISBLANK(yourTable[close_date])) ) )

Pete
- Gr33n
  Frequent Visitor
  3 years ago
  I was think of somthing more like this
  
  Thansk for the help! 😊
  - BA_Pete
    Super User
    3 years ago
    
    Ah, ok. In that case, you should just add another conditional column like this:
    
    caseStatus = if [close_date] = null then "Active" else "Closed"
    
    You can then just filter visuals/pages/reports on [caseStatus] = Active to only show values for currently active cases.
    
    Pete