Search multiple date fields

[aashton]

Hello,

I have a table Tickets, that has ID, owner, date created, date closed, date escalated, etc.  I want the user to be able to pick a date range from a slicer and have multiple cards that show how many tickets created, closed, escalated, etc within that time frame.  I'm confused as to how to set that up?  I presently have a Tickets table and a Date table, but only Date Created is linked to the Date table.

Thanks.

[Anonymous · Accepted solution]

Hi @aashton 

You could only create one active relationship between two tables( only Date Created is linked to the Date table).

Here I suggest you calcualte the count of Tickets which are in date range by measure.

My Sample:

Relationship is the same like yours.

Meausres:

Count of date created = COUNT('Sample'[date created])+0

Count of date closed = 
VAR _DateRange = VALUES('Date'[Date])
RETURN
CALCULATE(COUNT('Sample'[ID]),FILTER(ALL('Sample'),'Sample'[date closed] IN _DateRange))+0

Count of date escalated = 
VAR _DateRange = VALUES('Date'[Date])
RETURN
CALCULATE(COUNT('Sample'[ID]),FILTER(ALL('Sample'),'Sample'[date escalated] IN _DateRange))+0

Result is as below.

Date Range 9/28/2021 to 10/8/2021:

Date = 10/8/2021

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@aashton 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365