Forum Discussion
Mesure to count between 2 dates
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 |
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 )
3 Replies
- jdbuchanan71
Super User
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 )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