Mesure to count between 2 dates

[Anonymous]

Hello,

I am trying to create a measure that is able to count the number of distinct ticket numbers between two dates. I want to represent this in a matrix and a line graph, what I'm looking for is below. if I were to write it in a DAX measure formula it would look like this:

measure1 = calculate(distinctcount[ticket]),opendate >= x, close date <= x), however, I don't know what functions exist to filter the rows based on dates along an axis.

Data

Ticket	Open	Close
ticket 1	may 1	may 3
ticket 2	may 2	may 4
ticket 3	may 3	may 5
ticket 4	may 4	may 6
Ticket 5	may 5	may 7

Result

May 1	1
May 2	2
May 3	3
May 4	3
May 5	3
May 6	2
May 7	2

[jdbuchanan71 · Accepted solution]

@Anonymous 

It will be something like this.  Your model will have to have a date table and that is where you would pull the date from.  It does not have to be joined to your ticket table.

Ticket Count:=
VAR MinDate = FIRSTDATE ( DATES[Date] ) 
VAR MaxDate = LASTDATE ( DATES[Date] ) 
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Data[Ticket] ),
            Data[Close Date] >= MinDate,
            Data[Open Date] <= MaxDate
    )

[Anonymous]

Hello,

That's excellent! I will have to test this and verify that it gives me the output that I'm looking for. As I'm testing this, is there any reason the following wouldn't work?

I can foresee an issue with the USERELATIONSHIP() function as this may confuse the dates, but as per the filter of  NOT(Data[Ticket Task Name] = "Receive Unit"), would this cause an issue? I think that it is possible that it may not give the intended result because of the data structure, but would it cause an error?

Ticket Count:=
VAR MinDate = FIRSTDATE ( DATES[Date] ) 
VAR MaxDate = LASTDATE ( DATES[Date] ) 
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Data[Ticket] ),
            Data[Close Date] >= MinDate,
            Data[Open Date] <= MaxDate,
            USERELATIONSHIP(DATES[Date],Data[Open Date]),
            NOT(Data[Ticket Task Name] = "Receive Unit"),
    )

[jdbuchanan71]

I don't think it would cause an error but the dates would certainly cause your results to be different.  Instead of counting items where the open date <= the date it would only count items where the open date = the date