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 start date and a close date and wrote in the power query the following
"Age", each if [start_date] = null
then Date.From(DateTime.LocalNow())- [close_date] else null
It just returns null values. Any suggestion how to do it?
Thanks in thanks in advance
5 Replies
- BA_Pete
Super User
Hi Gr33n ,
I'm assuming that [start_date] can't actually be null, but that [close_date] will be null so you want to use today's date instead until it's actually populated:
if [close_date] = null then Date.From(DateTime.LocalNow()) - [start_date] else [close_date] - [start_date]Pete
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
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! 😊
