Forum Discussion

aashton's avatar
aashton
Helper V
4 years ago
Solved

Search multiple date fields

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 ho...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.