How to create a filter with two different crieteria on different tables?

[awganzarolli]

Hello, 

I have a table of opened and closed tickets. All I need to do is to create another table of dates and count how many tickets were opened (I mean, not closed) on that specific day. 

eg: 

If a have my tickets in a fact table like this:

i have to obtain a summarized table considering the status, open and last update columns in order to have how many tickets are open on that day. Using the same table above as an example:

it has show the historical data of how many tickets were "Under work" on that day. 

I´m very pleased you help me and thanks a lot.

[awganzarolli]

For that I was thinking to use filter formula like this: 

countrows(CALCULATETABLE(Tickets, Tickets[openDate] <= 'Tickets by Date'[Date], Tickets[UpdatedDate] <= 'Tickets by Date'[Date], Tickets[Status] = "Closed")))

But it return an multiple table reference error.

[v-yiruan-msft · Accepted solution]

Hi @awganzarolli ，

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a date dimension table: Tickets by Date

2. Create a measure as below to get the backlog

Backlog = 
VAR _curdate =
    SELECTEDVALUE ( 'Tickets by Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Tickets'[Id] ),
        FILTER (
            'Tickets',
            'Tickets'[Open] <= _curdate
                && IF ( 'Tickets'[Status] = "Closed", 'Tickets'[Last Update] >= _curdate, 1 = 1 )
        )
    )

Best Regards