Forum Discussion
Average Days with Filter on Status
I am running into an issue trying to get the average days between two dates. The issue is that the DAX I wrote keeps calculating today's date as the end for all data. I need to know the days betwee...
FreemanZ
Super User
Super Userhi madbaird
not sure if i get you, please try to plot a card visual with a measure like:
Measure =
VAR _table=
ADDCOLUMNS(
VALUES(data[item_id]),
"DayBetween",
CALCULATE(
VAR _value =
MAXX(
FILTER(data,data[Event_id]=4),
data[event_datetime]
)
RETURN
IF(
_value=BLANK(),
0,
INT(
_value-
MAXX(
FILTER(data,data[Event_id]=1),
data[event_datetime]
)
)
)
)
)
RETURN
AVERAGEX(
_table,
[DayBetween]
)it worked like:
Still didn't work out. I need the days between Created (1) and Resolved (4). I use the current date minus the created date if the table does not have a resolved date. Then I need to average it all out and be able to select a fiscal year filter from my date table to show the average day's tickets have been opened and if they are still open, to include them (Those without a resolved date).
| item_id | Event_Name | Event_id | event_datetime | AverageDaysBetween | Freeman_Code | What it is supposed to be | |
| 172390 | Created | 1 | 8/27/2019 | 1357 | 0 | ||
| 172390 | Activated | 2 | 8/27/2019 | 0 | |||
| 172390 | Responded | 3 | 8/27/2019 | 0 | |||
| 172390 | Resolved | 4 | 10/17/2019 | 43755 | 51 | ||
| 172390 | Closed | 5 | 10/21/2019 | 0 | |||
| 172391 | Created | 1 | 8/27/2019 | 1357 | 0 | ||
| 172391 | Activated | 2 | 8/27/2019 | 0 | |||
| 172391 | Responded | 3 | 8/27/2019 | 0 | |||
| 172391 | Resolved | 4 | 8/28/2019 | 43705 | 1 | ||
| 172391 | Closed | 5 | 9/2/2019 | 0 | |||
| 172392 | Created | 1 | 8/27/2019 | 1357 | 0 | ||
| 172392 | Activated | 2 | 8/27/2019 | 0 | |||
| 172392 | Responded | 3 | 8/27/2019 | 0 | |||
| 172392 | Resolved | 4 | 8/27/2019 | 43704 | 1 | ||
| 172392 | Closed | 5 | 9/2/2019 | 0 | |||
| 172393 | Created | 1 | 8/27/2019 | 1357 | 0 | ||
| 172393 | Activated | 2 | 8/27/2019 | 0 | |||
| 172393 | Responded | 3 | 8/27/2019 | 0 | |||
| 172393 | Resolved | 4 | 9/5/2019 | 43713 | 9 | ||
| 172393 | Closed | 5 | 9/9/2019 | 0 | |||
| 172394 | Created | 1 | 8/27/2019 | 1357 | 0 | ||
| 172394 | Activated | 2 | 8/27/2019 | 0 | |||
| 172394 | Responded | 3 | 8/27/2019 | 0 | |||
| 172394 | Resolved | 4 | 8/29/2019 | 43706 | 2 | ||
| 172394 | Closed | 5 | 9/2/2019 | 0 | |||
| 172395 | Created | 1 | 8/27/2019 | 1357 | 0 | ||
| 172395 | Activated | 2 | 8/27/2019 | 0 | |||
| 172395 | Responded | 3 | 8/27/2019 | 0 | |||
| 172395 | Resolved | 4 | 2/16/2023 | 44973 | 1269 | ||
| 172395 | Closed | 5 | 2/23/2023 | 0 | |||
| 18925 | Created | 1 | 2/20/2023 | 84 | This is based on the current date as it is not resolved yet. | ||
| 18925 | Activated | 2 | 2/20/2023 | ||||
| 18925 | Responded | 3 | 2/20/2023 | ||||
| Average | 202.4 |