Forum Discussion
Advocate IDAX Support with Dates and averages
I am still a novice with DAX. I am trying to get a measure that responses to the date slicer.
I have a task tracker that has a task open date and task closed date.
All open and closed tasks are in the same SharePoint list
I would like to get the average days to close each closed tasks.
I've tried filter and I've tried CALCULATE but I cannot get the right syntax.
Can someone get me started?
Hello FAW71 ,
You can follow steps to get average days to get number of days for each task, you can also add filter for closed only :
- Add a calculated column :
DaysToClose =
IF(
NOT(ISBLANK('YourTable'[TaskClosedDate])),
DATEDIFF('YourTable'[TaskOpenDate], 'YourTable'[TaskClosedDate], DAY),
BLANK()
)
- Create a measure to get average : AverageDaysToClose = AVERAGE('YourTable'[DaysToClose])
- Add this measure to a card visual or so.
I hope this helps.
Kindly mark this as solution or give a big thumbs up if this has solved your problem.
Cheers
Neeraj Kumar
- Add a calculated column :
6 Replies
- 123abc
Community Champion
could you please share your sample data file ?
FAW71There is sensitive data in my resource so I don't think it would be prudent. I am trying to understand DAX Context. I am not quite understanding row context vs filter context of some of the tutorials.
- divyed
Super User
Hello FAW71 ,
You can follow steps to get average days to get number of days for each task, you can also add filter for closed only :
- Add a calculated column :
DaysToClose =
IF(
NOT(ISBLANK('YourTable'[TaskClosedDate])),
DATEDIFF('YourTable'[TaskOpenDate], 'YourTable'[TaskClosedDate], DAY),
BLANK()
)
- Create a measure to get average : AverageDaysToClose = AVERAGE('YourTable'[DaysToClose])
- Add this measure to a card visual or so.
I hope this helps.
Kindly mark this as solution or give a big thumbs up if this has solved your problem.
Cheers
Neeraj Kumar
- Add a calculated column :
- DaniyalKhaleel1
Resolver I
Yes,Since you want the average days to close only for closed tasks, create a DAX measure.
Assuming your table is Tasks with Open Date and Closed Date:
Avg Days to Close =
AVERAGEX(
FILTER(
'Tasks',
NOT ISBLANK('Tasks'[Closed Date])
),
DATEDIFF(
'Tasks'[Open Date],
'Tasks'[Closed Date],
DAY
)
)
Important for the date slicer
If your slicer uses 'Tasks'[Open Date], the measure will respond to that slicer automatically.
If you want the slicer to filter based on Closed Date, you should ideally create a separate Date table and use the appropriate relationship.
For example:
Avg Days to Close =
AVERAGEX(
FILTER(
'Tasks',
NOT ISBLANK('Tasks'[Closed Date]) &&
NOT ISBLANK('Tasks'[Open Date])
),
DATEDIFF(
'Tasks'[Open Date],
'Tasks'[Closed Date],
DAY
)
)
This measure calculates the duration per task first, then averages those durations, which is generally what you want.
- Praful_Potphode
Hi FAW71
I have created multiple dax measures with sample data.Let me know if that is what you are looking for.
Please give kudos or mark it as resolved once confirmed.
Regards,
Praful
