Forum Discussion
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...
BA_Pete
Super User
Super User
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
I was think of somthing more like this
Thansk for the help! 😊
- BA_Pete
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