However, I would like to mix data in one page showing both currently open issues as well as how many were open during a time period. If I filter out the issues with status Closed, my tables showing the open numbers of issues sliced in different ways work well. But my graph counting open issues over time excludes them as well, which I don’t want.
I’ve tried every combination I can think of trying to use ALL, ALLSELECTED, KEEPFILTER, REMOVEFILTER,… in the DAX formula but I can’t get it to work.
here is the measure that should provide the correct result:
# of Open Issues in time =
VAR _lastvisibledate =
MAX ( Dates[Date] )
VAR _IssuesStatusInTime =
ADDCOLUMNS (
ALL ( issues ),
"StatusInTime",
SWITCH (
TRUE (),
_lastvisibledate >= Issues[Created Date]
&& (
ISBLANK ( Issues[Resolved Date] )
|| _lastvisibledate < Issues[Resolved Date]
), "Open",
_lastvisibledate < Issues[Created Date], BLANK(),
Issues[Status]
)
)
VAR _OpenIssues =
FILTER (
_IssuesStatusInTime,
[StatusInTime] = "Open"
)
RETURN
COUNTROWS ( _openIssues )
You can find the measure in pbix attached inserted in the line chart visual.
Notice that measure is not affected by status slicer, it always shows the number of opened issues in a selected period time.
If you are considering year month level the last day of the month is used as day to compare between creation and resolution date.
Thank you. I don’t think it’s 100% correct, because the numbers returned didn’t match what I wanted - but that was a very minor flaw and I think that with your code I managed to adapt it to give me my desired result.
By using MIN ( Dates[Date] ) I got my desired result. I think that you may have switched the operators around when comparing. Anyway, by using MIN instead of MAX I get my desired result of the 3, 2, 5, 4, 6 and 3 open issues in the months through April until September.
Thank you so much, I would never have been able to come up with that on my own.