Filter( dates based)

[Pingu]

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?

[v-yuta-msft · Accepted solution]

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