Hi all,
I've tried searching around and discovered this is a fairly common problem with various solutions, though I cant seem to implement one that works.
I'm trying to create a visual which will show in a line chart the number of active "connections" in a given month. Our business calls case files or enquiries "connections" - but it's a similar problem to calculating active employees or something like that - we have a start date and an end date for each connection, where a connection is still open the end date will be blank.
I have a Date Table, a connections table and another disconnected date slicer table.
The principle is the user chooses whatever date range they want, and the measure will calcualte which connections should be active for the chosen range:
Filter Connections Worked With =
var _rangeStart=FIRSTDATE('SlicerDateTable'[Date])
var _rangeEnd=LASTDATE('SlicerDateTable'[Date])
return
IF(
SELECTEDVALUE(SQLMain[c1_startdateLT]) <= _rangeEnd
&& (
SELECTEDVALUE(SQLMain[c1_enddateLT]) >= _rangeStart || ISBLANK(SELECTEDVALUE(SQLMain[c1_enddateLT]))
),
1,0
)
Then this measure is used within a second measure to count the active connections:
Count Connections Worked With =
CALCULATE(
DISTINCTCOUNT(SQLMain[ticketnumber]),
FILTER(
SQLMain,
SQLMain[Filter Connections Worked With] = 1
)
) + 0
This is fine, but I cannot then work out how to show the number of connections per month on a graph due to lacking a relationship between the table with connections in it (SQLMain) and the Date Table. Naturally I dont want to create a relationship with the date table and just the start date or the end date as this wont properly show on the graph.
Other implementations I've seen generally dont use a disconnected date table and measure to filter the thing being counted, so I havent been able to think of a good way to achieve the result. I do need to keep the disconnected date slicer table logic however as a lot of the report is built using this logic.
Any help with this would be appreciated!
