cancel
Showing results for
Did you mean:
Pingu Frequent Visitor

## Filter( dates based)

hi,

I have a table with tickets:

Ticket_ID  /    Day_Open   / Day_Closed / ...

and a Calendar table with dates

I'd like a measure to cound the amount of open tickets any given day so that I can chart it.

So  I tried:

OpenTickets=  COUNTROWS(FILTER(Table; Table[Day_Open].[Date]< MIN(Calendar[Date]) && (Table[Day_Closed].[Date]> MIN(Calendar[Date]) || Table[Day_Closed].[Date]= BLANK())))

but I doesn't work. I had to put MIN (Date)  and I suppose this is the problem, but I don't know how to filter using the given  checked day on a measure.

any tip?

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Filter( dates based)

Hi Pingu,

You said "I'd like a measure to cound the amount of open tickets any given day so that I can chart it.", if "any given day" means every day in the calendar table. You can create a relationship between Ticket table and Calendar table and modify your measure like below:

```OpenTickets =
COUNTROWS (
FILTER (
Table;
Table[Day_Open].[Date] < RELATED ( Calendar[Date] )
&& (
Table[Day_Closed].[Date] > RELATED ( Calendar[Date] )
|| Table[Day_Closed].[Date] = BLANK ()
)
)
)```

If it means date user has selected, after creating relationship, you can create a slicer chart based on Calendar column and modify measure like this:

```OpenTickets =
COUNTROWS (
FILTER (
Table;
Table[Day_Open].[Date] < SELECTEDVALUE ( Calendar[Date] )
&& (
Table[Day_Closed].[Date] > SELECTEDVALUE ( Calendar[Date] )
|| Table[Day_Closed].[Date] = BLANK ()
)
)
)```

Regards,

Jimmy Tao Community Support Team

## Re: Filter( dates based)

Hi Pingu,

You said "I'd like a measure to cound the amount of open tickets any given day so that I can chart it.", if "any given day" means every day in the calendar table. You can create a relationship between Ticket table and Calendar table and modify your measure like below:

```OpenTickets =
COUNTROWS (
FILTER (
Table;
Table[Day_Open].[Date] < RELATED ( Calendar[Date] )
&& (
Table[Day_Closed].[Date] > RELATED ( Calendar[Date] )
|| Table[Day_Closed].[Date] = BLANK ()
)
)
)```

If it means date user has selected, after creating relationship, you can create a slicer chart based on Calendar column and modify measure like this:

```OpenTickets =
COUNTROWS (
FILTER (
Table;
Table[Day_Open].[Date] < SELECTEDVALUE ( Calendar[Date] )
&& (
Table[Day_Closed].[Date] > SELECTEDVALUE ( Calendar[Date] )
|| Table[Day_Closed].[Date] = BLANK ()
)
)
)```

Regards,

Jimmy Tao